Forum Discussion

sanjaymithran's avatar
sanjaymithran
Helper II
6 months ago
Solved

Two measures with diffrent slicer selection for same column

Hi All, Thanks in advance,I tried with disconnected Date table but not getting what we want  
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    5 months ago

    Hi sanjaymithran , Thank you for reaching out to the Microsoft Community Forum.

     

    Yes, switching to TREATAS will perform better. TREATAS applies a proper column filter (like a virtual relationship), which allows the Storage Engine to handle the filtering efficiently instead of iterating the whole table in the Formula Engine.

     

    Rewrite your measure like this:

    Sales1 =

    CALCULATE(

        SUM(Data[sales]),

        TREATAS(VALUES(Slicer1[Quarter]), Data[Quarter]),

        Data[ProdGroup] = "Term",

        Data[Type] = "Actual"

    )

Naman01's avatar
Naman01
New Member
6 months ago

 

You can solve this by using two disconnected slicer tables and the TREATAS function. Each slicer needs its own table so that it only controls one measure.

Steps:

  1. Create two slicer tables (no relationships to your fact table):

Slicer1 = DISTINCT(SELECTCOLUMNS(Data, "Quarter", Data[Slicer1]))
Slicer2 = DISTINCT(SELECTCOLUMNS(Data, "Quarter", Data[Slicer2]))

  1. Write measures that apply each slicer separately:

Sales1 =
CALCULATE(
SUM(Data[sales]),
TREATAS(VALUES(Slicer1[Quarter]), Data[Slicer1])
)

Sales2 =
CALCULATE(
SUM(Data[sales]),
TREATAS(VALUES(Slicer2[Quarter]), Data[Slicer2])
)

  1. Put dim1 on rows and add both measures to your table visual.
    Now Slicer1 will only affect Sales1, and Slicer2 will only affect Sales2.

Example:

  • Slicer1 = Q1 2025, Slicer2 = Q2 2025 → dv1 shows 6 vs 8
  • Slicer1 = Q1 2025, Slicer2 = Q4 2025 → dv1 shows 6 vs 0, dv2 shows 0 vs 16

That should give you the exact results you were expecting.