March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
25 | |
18 | |
15 | |
9 | |
8 |
User | Count |
---|---|
37 | |
32 | |
18 | |
16 | |
13 |