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"

    )

QuentinGa's avatar
QuentinGa
Resolver I
6 months ago

Hi sanjaymithran,

You can try this:
Create 2 small tables with just your quarter names (Q1 2025, Q2 2025, etc.), don't link them to anything in your model. These are just for the slicers.


Then 2 simple measures:

Sales1 = CALCULATE( SUM(FactSales[sales]), TREATAS(VALUES(Slicer1[Quarter]), DimDate[Quarter]) )
Sales2 = CALCULATE( SUM(FactSales[sales]), TREATAS(VALUES(Slicer2[Quarter]), DimDate[Quarter]) )

 

In your visual: Put dim1 on rows, Sales1 and Sales2 as values. Add 2 separate slicers, one for each quarter table.


Now you can pick Q1 in one slicer and Q4 in the other, each measure respects its own slicer!

TREATAS is doing the magic here, it tells each measure which quarter to use from your actual date table.


Thanks in advance and kudos if this helps!

sanjaymithran's avatar
sanjaymithran
Helper II
6 months ago

Thanks QuentinGa,what the logic for DimDate[Quarter]? tried below logic not workout

Date Table
slicer1Qtr = "Q" & QUARTER([Date]) & " " & YEAR([Date])
slicer2Qtr = "Q" & QUARTER([Date]) & " " & YEAR([Date])
Dataset
QY = "Q" & QUARTER([CreateDate]) & " " & YEAR([CreateDate])
Sales1
CALCULATE(
SUM('Dataset'[Sales]),
      'Dataset'[QuarterYear]=SELECTEDVALUE('Date'[slicer1Qtr])
) 
Sales2
CALCULATE(
SUM('Dataset'[Sales]),
      'Dataset'[QuarterYear]=SELECTEDVALUE('Date'[slicer2Qtr])
)
Edit intraction used none for both slicers and matrix