Two measures with diffrent slicer selection for same column

[sanjaymithran]

Hi All,

Thanks in advance,I tried with disconnected Date table but not getting what we want

[v-hashadapu]

Hi @sanjaymithran , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @sanjaymithran , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Naman01]

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.

[Naman01]

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]

@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]

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]

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,

[danextian]

Hi @sanjaymithran 

One disconnected table should be enough. The other one should be a related table. Both though must include the quarter information. Since you're trying to show 0 if the value is blank as in the case of dv2 Sales1 on Table 1, you must also use a separate dimension table for dim column - you cannot assign a value to a row that doesnt exist  - there is no dv2 Q1 2025 in your data. 

Your relationships should look like below

Both Slicer 1 and 2 tables would be as below:

Add (or subtract) 0 to your sales measure to force 0 when the value is blank

Sales1 = 
SUM('Table'[sales]) + 0


Sales2 = 
--TREATAS to use a virtual relationship since Slicer2 is not related to your other tables
CALCULATE (
    [Sales1],
    TREATAS ( VALUES ( Slicer2[createdate] ), Slicer1[createdate] )
)
+ 0 

Create another measure as a visual filter to hide rows where Sales 1 and 2 are both zero

Filter = 
IF ( [Sales1] = 0 && [Sales2] = 0, 0, 1 )

Please see the attached pbix.

[mdaatifraza5556]

Hi @sanjaymithran 


1. Create calcualted col for year - qtr
2. Then Create two disconnected slicer table

3. Then create two measures 


I have attached the pbix for you reference


If this answers your question, kindly accept it as a solution and give kudos.

[sanjaymithran]

Thanks, need to apply filter in sales1 and sales2 measures example 

'Table'[dim1]="dv1" etc

[cengizhanarslan]

First, create TWO disconnected slicer tables. These have no relationships to anything. Then create the measure below in your use.

Sales1 :=
CALCULATE(
    SUM ( Fact[sales] ),
    KEEPFILTERS(
        TREATAS(
            VALUES( Slicer1_Quarter[YearQuarter] ),
            DimDate[YearQuarter]
        )
    )
)

Sales2 :=
CALCULATE(
    SUM ( Fact[sales] ),
    KEEPFILTERS(
        TREATAS(
            VALUES( Slicer2_Quarter[YearQuarter] ),
            DimDate[YearQuarter]
        )
    )
)

[QuentinGa]

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]

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

[FBergamaschi]

Hi @sanjaymithran,

can you please clarify what you are asking?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI