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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yuching_chang
Helper II
Helper II

How to use Dax to source different table in Matrix

Hi All,

 

I have a Matrix like this, at the Supplier level, I have indicator value differs across weeks

Capture.PNG

 

However, because the indicator value is calculated with complication, the values at  ID level can't simply be calculated by average or sum of values at Supplier level. Namely, currently when I collapse the matrix by ID, the value is false like below

 Capture1.PNG

 

As the Indicator calculation is complicated and needs sourcing many columns in DB, to avoid long loading time in PBI, I only grab the calculated Indicator from DB to my PBI table. To manage the value difference, I have 2 tables connected by "Primary Key".  

Picture2.jpg

 

Would like to know, is it possible to source Table 2 for Supplier level value while source Table 1 for ID level if user collapse by ID...? Or there's any other better solutions to do this? Thanks for any suggestion in advance!

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @yuching_chang ,

 

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

View solution in original post

9 REPLIES 9
v-deddai1-msft
Community Support
Community Support

Hi @yuching_chang ,

 

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

hi @v-deddai1-msft 

 

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

Picture1.png

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...

Capture3.PNG

 

Not sure if combined table can still serve the desired visuals..thanks in advance!

Hi @yuching_chang ,

 

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

 

 

 

Capture3.PNG

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

 

Hi @v-deddai1-msft 

 

thanks for the help! I tried, the value is correct at Supplier level and Location level when I collpase by location

Capture.PNG

 

But it's wrong at ID and CFG level...like below when I collapse by ID, the value is all 50...

Capture1.PNG

 

My raw data looks like below: (So ID 1 indicator should be 41, 33, 6, 34....each week)

 

I've tried many ways to adjust your dax for the issue but failed...can only reach out to you again

Really thank you for the help!

Seems like the raw data table failed to upload, post again

Capture2.PNG

Hi @yuching_chang ,

 

Would you please refer to my edited reply above?

 

Best Regards,

Dedmon Dai

Hi @v-deddai1-msft 

 

I've tried the updated Dax, but still doesn't work for my ID and Group level indicator value...And the matrix becomes a bit weird when I tried to collapse and expand (e.g. collapse ID 1, but then ID 1 disappears...)

 

Pls refer to the video below. I've recorded the issues encountered. Hope this way clearer. Thanks for the help in advance 🙂

Hi @yuching_chang ,

 

It will be nice if you can share the pbix file by onedrive for business in the video.

 

Best Regards,

Dedmon Dai

Hi @v-deddai1-msft 

 

thanks for the reply! I'd like to share the pbi file by business for onedrive, but I can  only share with people in my company network if no specific email to set up special access. So may I have email via private msg? Thanks!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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