I have 2 tables:
These 2 tables are connected to one another by a one to many relationship (1 Region -> * Products) through a column called RegionId which indicates where each product in the table ProductSales was sold.
My goal is to display in the Regions table the name of the most sold product which can be calculated from the ProductSales for each region. Would you be able to provide a smart and elegant way to write the correct DAX formula to achieve this task?
I think I need to use the function RELATEDTABLE() function and combine it with some additional function but I had no success thus far. This is what I tried so far:
GROUPBY('ProductSales', 'ProductSales'[RegionId], "Most Frequent Item",
FIRSTNONBLANK (
TOPN (
1,
VALUES(ProductSales[ProductKey]),
RANKX( ALL( 'ProductSales'[ProductKey]), COUNTROWS(CURRENTGROUP()),,ASC)
),
1
))
I was able to achieve my goal in 2 steps:
Step 1
Add a column in the table ProductSales which displays the most frequent item in column ProductKey for each RegionID:
Top Item =
VAR CurrRowTerritory = 'ProductSales'[RegionId]
VAR ProductKey =
SUMMARIZE (
FILTER ( 'ProductSales', 'ProductSales'[RegionId] = CurrRowTerritory ),
'ProductSales'[ProductKey],
"ProdCount", COUNT ( 'ProductSales'[ProductKey] )
)
RETURN
MAXX(TOPN ( 1, ProductKey, [ProdCount] ), 'ProductSales'[ProductKey])
Step 2
Use the RELATEDTABLE() function to import on Regions the information just created in ProductSales
TopItem = MAXX(RELATEDTABLE(FactInternetSales), 'FactInternetSales'[Top Item])
Note: my answer was inspired by this question.