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

 

  • sanjaymithran's avatar
    sanjaymithran
    Helper II
    6 months ago

    Naman01  Need to apply some static filters in the calculation

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

     

    Sales1- Type =Actual and ProdGroup=Term 

    Sales2 is diffrent 

    • v-hashadapu's avatar
      v-hashadapu
      Community Support
      6 months ago

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

       

      You don’t need to change your slicers, model or interactions. Your setup with disconnected slicers and TREATAS is already correct. To apply static filters (like Type and ProdGroup), just add them as additional filter arguments inside CALCULATE. DAX will combine the slicer filter (via TREATAS) and your fixed filters in the same filter context.

       

      For example:

      Sales1 =
      CALCULATE(
      SUM(Data[sales]),
      TREATAS(VALUES(Slicer1[Quarter]), Data[Quarter]),
      Data[Type] = "Actual",
      Data[ProdGroup] = "Term"
      )

       

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

      This keeps each slicer independent while still enforcing your static conditions. Avoid using SELECTEDVALUE comparisons on the fact column (like Dataset[QuarterYear] = SELECTEDVALUE(...)) because that creates a weaker scalar filter and can break context. TREATAS is the correct approach here since it creates a proper virtual relationship to your data grain.

      • sanjaymithran's avatar
        sanjaymithran
        Helper II
        6 months ago

        Hi v-hashadapu ,

         

        Thanks for your reply,I used below calculation but performance is very poor,is TREATAS will give better performance?

        Sales1 =
        CALCULATE(
        SUM(Data[sales]),
        FILTER('Data',Data[ProdGroup] = "Term"
        && Data[Type] = "Actual"
        && CONTAINS(VALUES(DateSlicer1),Slicer1[Quarter],Data[Quarter]))
        )

        Thanks,