topic Re: Comparing 2 Nested Slicers in a Table in DAX Commands and Tips

Comparing 2 Nested Slicers in a Table

Walt — Wed, 05 Oct 2022 16:37:56 GMT

Is there an easy way to compare the 2 nested slicers?
Example1:
Select: Slicer1 - Parent2, Group1, Type3 and Type4
Select: Slicer2 – Parent1, Group2 (Selecting all types in Group2)
Slicer1 = 5
Slicer2 = 10
Measures:
DivideSlicers = Divide(Slicer2,Slicer1) this would equal 2

Example2:
Select: Slicer1 – Type3
Select: Slicer2 – Type7
Slicer1 = 3
Slicer2 = 4
Measure:
DivideSlicers = Divide(Slicer2,Slicer1) this would equal 1.33

Slicer1

Parent1
- Group1
  - type1
  - type2
- Group2
  - Type5
  - Type6
  - Type7
- Parent2
  - Group1
    - Type1 - Value 6
    - Type3 – Value 3
    - Type4 – Value 2

Slicer2

Parent1
- Group1
  - type1
  - type2
- Group2
  - Type5 – Value 2
  - Type6 – Value 4
  - Type7 – Value 4
- Parent2…

Any help would be greatly appreciated.

Thank you,

Re: Comparing 2 Nested Slicers in a Table

v-easonf-msft — Thu, 06 Oct 2022 11:17:54 GMT

Hi， Walt

You need to copy a new table and filter the data separately by these two slicers to compare the results.

Result = SUM ( 'Table1'[value] ) / SUM ( 'Table2'[value] )

Best Regards,
Community Support Team _ Eason

Re: Comparing 2 Nested Slicers in a Table

Walt — Thu, 06 Oct 2022 18:10:10 GMT

I can currently get single data types from each slicer, however i cant get multiple types from a single slicer.

Re: Comparing 2 Nested Slicers in a Table

v-easonf-msft — Wed, 12 Oct 2022 08:17:55 GMT

Hi, Walt

Are you currently using a hierarchy slicer? What do 'single data type' refer to?

Please share a sample .pbix for further research.

Best Regards,
Community Support Team _ Eason

Re: Comparing 2 Nested Slicers in a Table

Walt — Tue, 18 Oct 2022 22:14:57 GMT

Yes, they are Hierarchy Slicers.
Single Data types like in the examples, 1Type 2Type and so on.
Here is another Example:
Slicer 1 I select..
From in Parent 1
in Group 2
1Type it has a value of 10

Then Slicer 2 Select 2 Types
From Parent 2
in Group 2
3Type with a value of 20
and
4Type with a value of 30

I would like to be able to compare the 1 selected type in Slicer 1 to the 2 selected types in Slicer 2.
So I could say... AVG the Slicer 2 Values to 25 or I could Sum them to 50 then compare them to the Slicer 1 Value of 10.
SUM(Slicer2) / SUM (Slicer1) or Average(Slicer2)/Average(Slicer1) or Average(Slicer2)/SUM(Slicer1)

Re: Comparing 2 Nested Slicers in a Table

v-easonf-msft — Wed, 19 Oct 2022 02:39:26 GMT

Hi, Walt

Still just confused by part of your explanation, whether your slicer is multi-select or not, the final result should be aggregated.

Do aggregation operations like sum and average of fields in the table work in your case?

Measure2 = Average(Slicer2[Value])/SUM(Slicer1[Value])

Best Regards,
Community Support Team _ Eason

Re: Comparing 2 Nested Slicers in a Table

Walt — Thu, 20 Oct 2022 22:47:41 GMT

Looking at your Example. I am trying to accomplish something different. I have a it setup as 3 seperate Tables. The Parent, Type, and Group are identical. Should I be doing something else?

Slicer1(Created with Dax):
- Parent
- Type
- Group
Slicer2(Created with Dax):
- Parent
- Type
- Group

DataValues (Data Source):
- Parent
- Type
- Group
- Values

Re: Comparing 2 Nested Slicers in a Table

Walt — Fri, 21 Oct 2022 00:29:45 GMT

I was able to find the solution here. Thanks for you help

https://community.powerbi.com/t5/Desktop/Two-slicer-on-same-column/m-p/516318/thread-id/241351