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"

    )

danextian's avatar
danextian
Super User
6 months ago

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.