Forum Discussion

Pri's avatar
Pri
Frequent Visitor
7 years ago
Solved

One slicer from two different tables

I have two tables as shown in below image. Where table1 one has some column along with a field Total Value and another table has somef fields along with the field Internal Value. Now, I want to creat...
  • Greg_Deckler's avatar
    7 years ago

    Pri , re-read this post. Not sure but this seems like a use case for the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563.

     

    Attached a PBIX with a possible solution. You want the TotalValues, InternalValues and Slicer tables. The measure is:

     

    Measure = 
    VAR __measure = MAX('Slicer'[Column1])
    RETURN
        SWITCH(TRUE(),
            __measure = "Total Value",SUM('TotalValues'[Total Value]),
            __measure = "Internal Value",SUM('InternalValues'[InternalValue]),
            __measure = "External Value",SUM('TotalValues'[Total Value]) - SUM('InternalValues'[InternalValue]),
            -1
        )

    Critically, this assumes that your two tables are related in some fashion. If not, you will have to use LOOKUPVALUE or something along those lines to relate the Total Value to the Internal Value.