Forum Discussion
How to use Dax to source different table in Matrix
Hi Anonymous ,
According to your screenshot, you data has a hierarchy(ID->Location->Supplier), right? Based on your sample data, please try to use the following dax(Since I don't know how you calculated the location value, I set it to sum of suppliers
Measure = IF ( ISINSCOPE ( Table2[Location] ), IF ( ISINSCOPE ( Table2[Supplier] ), SUM ( Table2[Indicator] ), CALCULATE ( SUM ( Table2[Indicator] ), FILTER ( Table2, Table2[Location] IN DISTINCT ( Table2[Location] ) ) ) ), CALCULATE ( MAX ( Table1[Indicator] ), Table1[ID] IN DISTINCT ( Table1[ID] ) ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
really thank you for the instruction and it does work!! But still one related question: is it possible to consolidate all data in 1 table and leverage this Dax? like below
The reason is I actually have indicator value differences at ID, Location, Supplier levels (maybe more later), and found if I separate into tables, I'll have too many tables...However, if i have the tables combined into 1, the Matrix will display the aggregated value as "All"...but I still need the Matrix to run the originally desired function - to show aggregated value only when users collapse...
Not sure if combined table can still serve the desired visuals..thanks in advance!
Community SupportHi Anonymous ,
Please refer to the measure:
Measure =
IF (
ISINSCOPE ( 'Table'[Location] ),
IF (
ISINSCOPE ( 'Table'[Supplier] ),
SUM ( 'Table'[Indicator] ),
CALCULATE (
SUM ( 'Table'[Indicator] ),
'Table'[Supplier] = "ALL"
)
),
CALCULATE ( SUM( 'Table'[Indicator]),FILTER(ALL('Table'[Location]),'Table'[Location] = "ALL" )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai