Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
Long time being only a anonymous visitor and a self-learner, today I have to ask for help.
1st) The objective:
Based on the below example data:
identifier | rank | Month |
A | 1 | 2 |
A | 1 | 2 |
A | 2 | 3 |
A | 3 | 4 |
A | 3 | 4 |
A | 4 | 5 |
A | 4 | 5 |
B | 1 | 4 |
B | 2 | 5 |
B | 3 | 6 |
B | 3 | 6 |
C | 1 | 3 |
C | 1 | 3 |
C | 2 | 4 |
C | 3 | 5 |
D | 1 | 6 |
And considering a user filter that includes the months 2, 3 and 4 I am tryng to get to this desired output
Identifier | Rank |
A | 3 |
B | 1 |
C | 2 |
What it could be read as the max of column Rank based on the contextual filter.
The final output will be printed on a bar chart that will count the number of Identifiers grouped by Rank.
2nd) The attempts:
The best attemp that I made was use a summarize, and create a new table, the problem was that the table is not filtered by the contextual filter, so I do not get the max of rank based on the filtered date, only the max of the rank. The code used was the following:
SUMMARIZE(allselected(exampledata), exampledata[identifier],"max",CALCULATE(max(exampledata[rank]),allselected(exampledata)))
Someone could please point me in the right direction, if this should be resolved by modeling or by a dax expression.
Interesting question. did you find the solution? I am also facing a similar problem
Hi Paulo1,
To achieve this, create a measure using DAX below:
Result = CALCULATE(MAX(exampledata[rank]), ALLEXCEPT(exampledata, exampledata[identifier]), FILTER(exampledata, exampledata[Month] = 2 || exampledata[Month] = 3 || exampledata[Month] = 4))
Regards,
Jimmy Tao
Hello v-yuta-msft
I work with Paulo1.
Thanks for your reply, but I need create a column because a measure can't use in group.
The final output will be printed on a bar chart that will count the number of Identifiers grouped by Rank.
Regards,
Tiago Bohrer
Hi Paulo1,
How does your desired output comes? Could you clarify more logic of this?
Regards,
Jimmy Tao
Hi Jimmy Tao,
The final output is the chart.
You could consider each identifier as a unique person, so the y-axis is the column "rank" and the x-axis is the count of people (identifier). However this count should only consider one person (identifier) in other words a distinct count. Besides this, to each person (identifier) the only associated rank (y-axis) should be the maximum rank considering the month filter from the user. So I believe that the count will "operate" over a table with only one record to each person (identifier).
I hope that I have clarified the desired output.
Best Regards,
Paulo
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |