Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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   However, because the indicator value is calculated with complication, the values at...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 🙂

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
6 years ago

Hi Anonymous ,

 

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

 

Best Regards,

Dedmon Dai

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!