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"

    )

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
5 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,

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    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"

    )