Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone, I am struggling with the TopN function and I hope you can help me out.
Data: event registration data, with calculated columns for CY (current year) and LY (Last year). I also created a measure to show the count difference between them, “CY-LY Reg Diff”
Goal: create a card that displays the region and store type with the largest deficits year-on-year (smallest value for “CY-LY Diff” measure)
Challenge: I have tried this formula, but it gives me “blank” as a region.
Largest Deficit Region = CALCULATE(SELECTEDVALUE('Sample Data'[Region]),TOPN(1,'Sample Data',[CY - LY Reg Diff],ASC))
Year | Store Type | Region | Registered | Last year | Current year |
Year 1 | Store Type A | West | 1 | 1 | 0 |
Year 1 | Store Type C | South | 1 | 1 | 0 |
Year 1 | Store Type C | Midwest | 1 | 1 | 0 |
Year 1 | Store Type B | West | 1 | 1 | 0 |
Year 1 | Store Type B | West | 1 | 1 | 0 |
Year 1 | Store Type B | West | 1 | 1 | 0 |
Year 2 | Store Type C | West | 1 | 0 | 1 |
Year 2 | Store Type B | Midwest | 1 | 0 | 1 |
Year 2 | Store Type B | South | 1 | 0 | 1 |
Year 2 | Store Type C | Midwest | 1 | 0 | 1 |
Any help or tips would be greatly appreciated.
Thank you.
Solved! Go to Solution.
This piece of code:
Largest Deficit Region =
CALCULATE(
SELECTEDVALUE('Sample Data'[Region]),
TOPN(1,
'Sample Data',
[CY - LY Reg Diff],
ASC
)
)
returns BLANK because SELECTEDVALUE returns BLANK when there is more than 1 region returned.
Change the expression under CALCULATE to show the number of regions - distinctcount( 'sample data'[region] ) - and you'll see it's not 1.
Please read this: https://dax.guide/topn/
Best
D
Could you use ADDCOLUMNS to add your TOPN column and then take the MIN or MAX of it?
Hi, I must be doing something wrong here. I added ADDCOLUMNS to the expression, but now it looks like it's using it as multiple columns- I thought that I only added one. 😞
This piece of code:
Largest Deficit Region =
CALCULATE(
SELECTEDVALUE('Sample Data'[Region]),
TOPN(1,
'Sample Data',
[CY - LY Reg Diff],
ASC
)
)
returns BLANK because SELECTEDVALUE returns BLANK when there is more than 1 region returned.
Change the expression under CALCULATE to show the number of regions - distinctcount( 'sample data'[region] ) - and you'll see it's not 1.
Please read this: https://dax.guide/topn/
Best
D
Thank you- I was actually able to get it to work!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |