Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Please help!
Seriously, I've tried every permutation of measure/column. I can see the finish line, but I can't get over it.
Here's my table of data. You can clearly see the CustCount measure is working...
So, why on earth wouldn't either of my RANKX attempts work? What's so hard about ranking a measure that's working?
dRanker is a calculated column...
dRanker = RANKX(ALL(FW17), [CustCount])
dRanker2 is a calculated measure...
dRanker2 = RANKX(ALLSELECTED(FW17), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))
CustCount is a calculated measure as well...
CustCount = DISTINCTCOUNT(FW17[customerkey])
Pulling my hair out here... how on earth is this difficult?
I've checked the other solutions on this forum for the exact title of mine, and they all don't work.
Just for grins, here's my model...
Solved! Go to Solution.
Hi gjohnson900,
Modify your measure CustCount like pattern below:
CustCount = Calculate(DISTINCTCOUNT(FW17[customerkey]), ALLEXCEPT(FW17, FW17[source]))
And check if it can meet your requirement.
Regards,
Jimmy Tao
Hi gjohnson900,
Modify your measure CustCount like pattern below:
CustCount = Calculate(DISTINCTCOUNT(FW17[customerkey]), ALLEXCEPT(FW17, FW17[source]))
And check if it can meet your requirement.
Regards,
Jimmy Tao
That allowed me to rank just the [source], yes. Thanks Jimmy. I'm closer than I've been able to get by implementing this, but I still have some outstanding issues that I'll bring up in another thread.
Thanks.
Here's the gist gang...
Instead ofthe dRanker2 DAX that I was using...
dRanker2 = RANKX(ALLSELECTED(FW17), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))
I had to specify the column that I wanted ranked...
dRanker2 = RANKX(ALLSELECTED(FW17[source]), CALCULATE(DISTINCTCOUNT(FW17[customerkey])))
In that regard, it's a lot like the PARTITION clause when using RANK() in tSQL.
I am guessing that it is the context in which your measure is evaluating within the column that is the problem. You might try wrapping that measure in a CALCULATE with a RELATED or RELATEDTABLE or ALL filter of some sort. Really difficult to say without sample/example raw data.
In other words, your measure is calculating fine within the table visualization, but that is within a certain context. When you evaluate that same measure in a table as part of a calculated column, then it is most probably an entirely different context. Often, what I do is to replicate the context of my table visualization by creating a temp table using FILTER or SUMMARIZE, etc. Then I use ADDCOLUMNS to add in my measure. Then I can can use another ADDCOLUMNS to add in my RANKX calculation to that temporary table. Then I just have to filter down the temp table to the row that I want for my RETURN (or use an "X" aggregation function like SUMX, MAXX, etc.)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |