Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
denpries
Resolver I
Resolver I

calculate() over virtual table

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?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.