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
lcd
Frequent Visitor

New table with measures values

Hi everyone, 

my case is that:

- I have a main table with measures based on other fact tables and a calendar table

- now I want to build a table with DAX code (with SELECTCOLUMNS) to hold the measures values in a new table.

 

To this point, no problem.

The problem is that the visuals based on this new table are not affected by the calendar table values. I thougt that I could use the calendar table values because they are in the measures calculation. In fact, the visuals based on measures are affected by the calendar values, but not the new table's.

 

Any ideas?? 

1 ACCEPTED SOLUTION

Hi @lcd,

 

It is the expected behavior.

 

Not like measures, calculate columns/tables(like your myMeasureTableare computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report(like a Slicer).

 

So it is not possible to create a calculate table which can change dynamically with user selections on the report. Smiley Happy

 

Regards

View solution in original post

10 REPLIES 10

Hi there,

 

So if the DAX measure is written correctly, it should work the same way regardless of what table it is held in. You just need to make sure that any column reference in the DAX measure includes both the table and column name. Unless I'm misunderstanding the question. I just posted an article about measures tables today actually, not sure if that's coincidence or that you read that and ended up here with your question.

 

Reid Havens - Principal Consultant

PowerPivotPro

Thanks Reid, but I need more help and so I give more information:

 

My 3 tables:

- EXPENSES: it's a fact table with expenses transactions.

- CALENDAR: It has all the dates I need and it's linked with EXPENSES by a date column.

- COMPANY: it's a table with companies info and it's linked with EXPENSES by companyID. In this table I have the measure:

 

myMeasure = TOTALYTD(sum(EXPENSES[AMOUNT]);CALENDAR[DATE]) 

 

Now, I need to have a new table with myMeasure:

 

myMeasureTable = SELECTCOLUMNS(COMPANY;"companyId";COMPANY[companyId];"myMeasureField";COMPANY[myMeasure])

 

When I use a visual with COMPANY[myMeasure] and a slicer with CALENDAR[DATE], I can control the visual with the slicer.

But when I use a visual with myMeasureTable, the slicer with CALENDAR[DATE] doesn't work.

 

Thanks

Hi @lcd,

 

It is the expected behavior.

 

Not like measures, calculate columns/tables(like your myMeasureTableare computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report(like a Slicer).

 

So it is not possible to create a calculate table which can change dynamically with user selections on the report. Smiley Happy

 

Regards

Thanks @v-ljerr-msft, I understand. I thought this kind of tables were like measures. I will look for other solution.

Regards

 

Hello @lcd,

I don't know if you still haven't found a solution for this topic, but here is what I've done.

1) Created a disconected table with the categories I wanted to show in my waterfall chart;

 

 disc_table.JPG


2) Created a measure using SWITCH to display the wanted value for each category;


WATERFALL VAR =
SWITCH(MAX(p_gross_margin_variances[id_var_factor]);
1;[BL05BT-GROSS MARGIN];
2;[BL03VR-PRICE VAR];
3;[BL04VR-COST VAR];
4;[BL05VR-GROSS MARGIN VAR];
5;[BL03VR-VOLUME VAR];
6;[BL03VR-MIX VAR];
7;[BL03VR-MIX AND VOL VAR];
8;[BL05AC-GROSS MARGIN];
BLANK())


3) Put the categories and values in their respective fields and voilá:

 waterfall.JPG

 

The only thing is that you'll have to consider the Total bar as the Actual Value since it's not possible to change the bar name.

 

To do that you can use the Ultimate Waterfall from dataviz.

 

Hope it helped!

 

Regards

 

Regards @rbaleche  good solution help me a lot.

 

Thank you @rbaleche your solution helped me a lot!

 

I created multiple SWITCH measures so I could make a matrix with different measures on each cell.

Hi, can you explain in detail how to do it?

This is awesome, except that I get the last value as the total amount. Was wondering where am I going wrong.

@rbaleche you saved me!! thank you!!

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.