cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## 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:

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

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

1 ACCEPTED SOLUTION
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))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

Hey @LABORSAL ,

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
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))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

That does, the trick, thanks!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors