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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LABORSAL
Advocate II
Advocate II

Virtual table referencing in measures

Hello everyone,

 

I am trying to get a KPI for BIAS which will allow me to count the number of rows at a summarized level which fall into a range.

 

The best idea I've had is to create a summarized table as a variable and then use it inside a calculate. Here I am trying to get the number of "green bars" which would be everything between -5% and 5%:

 

 

GreenBars = 
VAR SumTable = 
  SUMMARIZECOLUMNS(
      'Table'[Year],
      'Table'[Period],
      "BIAS", CALCULATE( [BIAS Base] )
)

Return
CALCULATE( COUNTROWS(SumTable),FILTER(SumTable, SumTable[BIAS] <= .05 && SumTable[BIAS] >= -.05))

 

 

*[BIAS Base] is a SUMX measure already calculated*

 

I need to do this against a summarized table because I have way more detail in the complete table but the information I am trying to show should be at the "Year Period" level.

 

So for example if I have the filters for this information I'd need for the KPI to be 2:

image.png  

 

However, if I change my filters I'd need to get a different answer (3 in this case)

image.png

 

Is it possible to do this virtual table referencing inside measures?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Yes, I do it all the time, but your code would be:

 

GreenBars = 
VAR SumTable = 
  SUMMARIZECOLUMNS(
      'Table'[Year],
      'Table'[Period],
      "BIAS", CALCULATE( [BIAS Base] )
)

Return
COUNTROWS(FILTER(SumTable, [BIAS] <= .05 && [BIAS] >= -.05))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

Hey @LABORSAL ,

 

the simple answer to your question is: yes.

It's very common to create a table (a virtual table) inside a measure, and assign this table to a variable.

And use this variable further down the measure.

 

For this, I'm wondering what is your exact question.

 

By, the way it's not necessary to wrap the reference to the measure [BIAS Base] into a CALCULATE, as referencing a measure implicitly is exactly doing this.

 

If your measure does not return the exact result, please provide a pbix file that contains sample data but still reflects your data model. Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the Excel file as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Greg_Deckler
Super User
Super User

Yes, I do it all the time, but your code would be:

 

GreenBars = 
VAR SumTable = 
  SUMMARIZECOLUMNS(
      'Table'[Year],
      'Table'[Period],
      "BIAS", CALCULATE( [BIAS Base] )
)

Return
COUNTROWS(FILTER(SumTable, [BIAS] <= .05 && [BIAS] >= -.05))


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

That does, the trick, thanks!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.