Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
See the attached PBIX
The DimDate table has a one to many relationship with FactSales.
The DimDate table has a separate year, quarter and month column which I want to use rather than the default date hierarchy.
The report has 3 visuals:-
Each report uses DAX measure
I need a little DAX help please with report 3) to rank the month with the highest sales for each quarter by year
For example:-
Thank you
Solved! Go to Solution.
Hi @speedramps
I try to modify the DAX expression you wrote, I just replace DimDate[MonthLongName] with DimDate[MonthNum]
What’s yours:
Rank by year quarter month =
RANKX ( ALL ( DimDate[MonthLongName] ), [Total quantity] )
New:
Rank by year quarter month (2) =
RANKX ( ALL ( DimDate[MonthNum] ), [Total quantity] )
It is important to note that you must remove the DimDate[MonthLongName] column,
and replace it with DimDate[MonthNum] in the table visual,
otherwise you will not get the correct results.
Sample data:
Result:
Is this the result you want? Hope this is useful to you
Please feel free to let me know if I misunderstand your question
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @speedramps
I try to modify the DAX expression you wrote, I just replace DimDate[MonthLongName] with DimDate[MonthNum]
What’s yours:
Rank by year quarter month =
RANKX ( ALL ( DimDate[MonthLongName] ), [Total quantity] )
New:
Rank by year quarter month (2) =
RANKX ( ALL ( DimDate[MonthNum] ), [Total quantity] )
It is important to note that you must remove the DimDate[MonthLongName] column,
and replace it with DimDate[MonthNum] in the table visual,
otherwise you will not get the correct results.
Sample data:
Result:
Is this the result you want? Hope this is useful to you
Please feel free to let me know if I misunderstand your question
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @speedramps and @v-angzheng-msft
This unexpected behavior is a "side effect" of using the Sort by Column feature!
When a DAX expression removes a filter from a column that has the Sort By Column setting active,
it is a best practice to always include both columns in the ALL function.
Read more about it here
What this simply means is that your measure will work with the [MonthLongName] column but because you've sorted it by the [MonthNum] Column you have to include both columns in the ALL function just like this:
Rank by year quarter month =
RANKX (
ALL (
DimDate[MonthNum],
DimDate[MonthLongName]
),
[Total quantity]
)
Hope this helps! 😃
Sean
Thank you
It is still not working.
I want to rank months byhighest sales each quarter
Click here for PBIX version 2
@speedramps , You need to try measures like
Year Rank = Rankx(Allselected('Date'[Year]), [Sales],,desc,dense)
Qtr Year Rank = Rankx(Allselected('Date'[Qtr Year]), [Sales],,desc,dense)
Qtr in Year Rank = Rankx(filter(Allselected('Date'[Qtr Year], 'Date'[Year]),'Date'[Year] =max('Date'[Year])), [Sales],,desc,dense)
Month, in Qtr in Year Rank = Rankx(filter(Allselected('Date'[Month Year],'Date'[Qtr Year], 'Date'[Year]),'Date'[Year] =max('Date'[Year])
&& 'Date'[Qtr Year] =max('Date'[Qtr Year])), [Sales],,desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
Thank you
It is still not working.
I want to rank months byhighest sales each quarter
Click here for PBIX version 2
User | Count |
---|---|
85 | |
74 | |
71 | |
68 | |
56 |
User | Count |
---|---|
96 | |
94 | |
92 | |
78 | |
71 |