One slicer from two different tables

[Pri]

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 create a slicer which filters based on : Total Value, External Value and Internal Value where External Value = Total Value - Internal Value.

Can someone please look into this asap.

Many thanks

Regards

[Greg_Deckler · Accepted solution]

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

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.

[Pri]

@Greg_Deckler : In addition to the above, I have following measures which were earlier working on Total Value column, however these now need to be dynamic based on the slicer selection.

CY = SUMX(FILTER(Query1,Query1[Year_Label] = "CY"),Query1[Total Value])

PY = SUMX(FILTER(Query1,Query1[Year_Label] = "PY"),Query1[Total Value])

I did try to replace it with the new measure as below but it doesn't work.

PY = SUMX(FILTER(Query1,Query1[Year_Label] = "PY"),[Measure])

Regards

[Pri]

Thanks a lot. This works perfectly fine. 🙂

[Greg_Deckler]

I would suggest creating a calculated column that performs that calculation and returns 0 and 1 or true/false, etc. depending upon the outcome. Then use that column as your slicer.

[Pri]

Can you please share the complete formula?

@Greg_Deckler