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

 

 

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

 

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!

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

9 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    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 

       

      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!

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

        Hi 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