Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi. I basically have a proof of concept test.
Purpose is to return the name of the item at a certain rank. This all is in a measure, as it cannot be done in a calc column because then it does no longer respond to slicers.
However, cant get it working
//The measure we make here is used on a line chart, as 'value' measure
IDatrankR = //x is a list on a seperate table with values 1..1000. This one is used on the line chart on the X-axis. VAR r = values(x) //we therefore get for each position on the line chart a single value back for r here //Virtual table basically rankes the values in the 'values' table from lowest to highest and stores this value in new column 'ranking' VAR VTABLE = SUMMARIZE('values';'values'[id];"Ranking";RANKX(ALLSELECTED('values');[sumvalue];;DESC;Dense)) //i want to have the id at rank = r. //i just think i cant do it this way; calculate apparently cannot be used with a virtual table VAR id_at_r = CALCULATE(values(VTABLE[id]);filter(VTABLE;[Ranking]=r)) RETURN id_at_r
The annoying thing is here: if 'id' would be numeric i could simply do
VAR id_at_r = sumx(filter(VTABLE;[Ranking]=r);'values'[id])
As this perfectly returns the id in this case. But my id is not numeric.
A workaround would be an numeric index next to the non-numeric name and sumx-ing that index + do a lookupvalue, but thats just not so elegant.
I think i am just not allowed to do the calculate on the virual table. Is there another smart way?
Solved! Go to Solution.
Hi @denpries
Your approach is very similar to that presented in this SQLBI article 🙂
In fact, to fix the last step you can follow the method in the article above and use MINX to extract the id_at_r from the virtual table (since MINX can handle values of text & number types):
VAR id_at_r = MINX ( FILTER ( VTABLE; [Ranking] = r ); 'values'[id] )
Regards,
Owen
Hi @denpries
Your approach is very similar to that presented in this SQLBI article 🙂
In fact, to fix the last step you can follow the method in the article above and use MINX to extract the id_at_r from the virtual table (since MINX can handle values of text & number types):
VAR id_at_r = MINX ( FILTER ( VTABLE; [Ranking] = r ); 'values'[id] )
Regards,
Owen
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |