Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Could someone give me the M code or DAX formula to achieve the result in column Rank? Many thanks!
Type Date Rank
Type A 01/01/17 1
Type A 01/02/17 2
Type A 01/03/17 3
Type B 01/01/18 1
Type B 01/02/18 2
Type B 01/03/18 3
Type B 01/04/18 4
Solved! Go to Solution.
As a calculated column..you can use
Column = RANKX ( FILTER ( Primary_ID, [Type] = EARLIER ( [Type] ) ), [Date], , ASC, DENSE )
Create a new Column and Measure and add the folowing DAX code:
Rank = VAR d = Table[Date] VAR a = Table[Type] RETURN CALCULATE ( RANK.EQ ( d, Table[Date], ASC ), FILTER ( ALL ( 'Table' ), Table[Type] = a ) )
Thanks.
I've tried this..
=CALCULATE(RANK.EQ(Primary_ID[Date],Primary_ID[Date],ASC),FILTER(all(Primary_ID),Primary_ID[Type]=Primary_ID[Type]))
But get the error..
A single value for column 'Date' in table 'Primary_ID' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
You have to declare the variables outside the DAX formula and then call them inside (as shown on the formula sent).
They are immutabe that's why i used them.
If you try it as shown, you will see it works this way
As a calculated column..you can use
Column = RANKX ( FILTER ( Primary_ID, [Type] = EARLIER ( [Type] ) ), [Date], , ASC, DENSE )
Perfect! That's the answer thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |