Forum Discussion

Hassan_346's avatar
Hassan_346
Regular Visitor
3 years ago
Solved

Comparison visuals using a single slicer.

Hi, I have a comparison report that is currently fetching data from two tables named "Sheet 1" and "Sheet 2." The report is divided into two parts, each with a slicer from a different column of th...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Hassan_346 ,

    You can follow the steps below to get it, please check if that is what you want.

    1. Do not create any relationship between 'Sheet1' and 'Sheet2'

    2. Create a measure as below

    Sum of drink = 
VAR _selinstitutes =
    ALLSELECTED ( 'Sheet1'[Institute] )
VAR _types =
    CALCULATETABLE (
        VALUES ( 'Sheet1'[Type] ),
        FILTER ( 'Sheet1', 'Sheet1'[Institute] IN _selinstitutes )
    )
VAR _links =
    CALCULATETABLE (
        VALUES ( 'Sheet1'[Link] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Type] IN _types )
    )
RETURN
    CALCULATE (
        SUM ( 'Sheet2'[Drink(1 or 0)] ),
        FILTER ( 'Sheet2', 'Sheet2'[Link] IN _links )
    )

    Best Regards

Hassan_346's avatar
Hassan_346
Regular Visitor
3 years ago

Hi Anonymous ,

Thanks for the response. I can't access the file. However, I did create a relationship between the two tables using links. but the sum of drink for Circle still comes to 1, while it should be 2. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Hassan_346 ,

    You can follow the steps below to get it, please check if that is what you want.

    1. Do not create any relationship between 'Sheet1' and 'Sheet2'

    2. Create a measure as below

    Sum of drink = 
VAR _selinstitutes =
    ALLSELECTED ( 'Sheet1'[Institute] )
VAR _types =
    CALCULATETABLE (
        VALUES ( 'Sheet1'[Type] ),
        FILTER ( 'Sheet1', 'Sheet1'[Institute] IN _selinstitutes )
    )
VAR _links =
    CALCULATETABLE (
        VALUES ( 'Sheet1'[Link] ),
        FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[Type] IN _types )
    )
RETURN
    CALCULATE (
        SUM ( 'Sheet2'[Drink(1 or 0)] ),
        FILTER ( 'Sheet2', 'Sheet2'[Link] IN _links )
    )

    Best Regards