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 

 

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

 

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

 

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!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Would you please refer to my edited reply above?

     

    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
        Icon for Community Support rankCommunity 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