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
TCavins
Helper V
Helper V

Summarize Measures Into One Column

I have a slicer on a Date table named Dates. The date table is not linked to anything, it is just used to help populate the values of various measures used in another table, currently called Query2.

 

An example of one of my measures is below and updates as expected if the slicer on the 'Dates' table changes.

 

My First Measure = CALCULATE(COUNTROWS(Query2), FILTER(Query2, Query2[Date_Placed] >= MIN('Dates'[Date]) && Query2[Date_Placed] <= MAX('Dates'[Date])))

 

 

 

My table visualization displays the measures correctly as separate columns. If I adjust the slicer, the table updates as well. Now, I want to combine all of the measures into one column. I've tried the following but it does not take into affect the slicer. It pulls the static data from when the report loaded and I get incorrect values that do not update as the slicer changes.

 

 

Table = 

Var t1 = 
    summarize(DISTINCT(query2), Query2[Category], Query2[Type], "Measure", "My First Measure", "Totals", [My First Measure])

Var t2 =
    summarize(DISTINCT(query2), Query2[Category], Query2[Type],  "Measure", "My Second Measure", "Totals", [My Second Measure])

RETURN UNION(t1,t2)

 

 

 

Any help on how I can throw all my measures into one column and still retain the dynamic value influenced by the Slicer?

2 ACCEPTED SOLUTIONS

Hi @TCavins ,

 

you can add the categories on the rows of the matrix visualization and the information will be presented has you need:

 

MFelix_0-1613752674800.png

 

Second inmage is turning off the stepped layout.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-shex-msft
Community Support
Community Support

HI @TCavins,

As MFelix said, measures are content-based expressions.
If you use them to calculate column/table, their row contents will be fixed and not dynamically calculated with row contents and correspond filter effects.

For your requirements, you can create an attribute table with all measure names.
Then you can use this new field on matrix row, use a measure expression to replace the raw value field to return correspond results based on current category values.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @TCavins,

As MFelix said, measures are content-based expressions.
If you use them to calculate column/table, their row contents will be fixed and not dynamically calculated with row contents and correspond filter effects.

For your requirements, you can create an attribute table with all measure names.
Then you can use this new field on matrix row, use a measure expression to replace the raw value field to return correspond results based on current category values.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
MFelix
Super User
Super User

Hi  @TCavins ,

 

You cannot use measure to create tables or column into your model. Measures are context based and only calculated at the time you call them. This means that if you want to calculate the sum of a value using the slicers and filters the calculation is made.

Calculated columns are row based and the context is of the table they are.

 

What is the purpose of this table that you are creating? is to have the visualization of those measure in a single column on the same visualization

 

If this is the purpose use a matrix and then on the options turn on the Show on Rows that will create a matrix but the measures will be presented on rows as you can see below:

 

MFelix_0-1613750806945.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



It's closer to what I want.

 

Category 1

     Measure 1     12

     Measure 3     18

Category 2

     Measure 1     13

 

Anyway to now split the measures into a column next to the Category and totals after that?

Hi @TCavins ,

 

you can add the categories on the rows of the matrix visualization and the information will be presented has you need:

 

MFelix_0-1613752674800.png

 

Second inmage is turning off the stepped layout.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.