DAX Help

[AnkitaaMishra]

Hi All,

I am trying to achieve a scenario where I have 3 visuals in the Page and few slicers like "Brand", "Competitor Brand", "Start Date",etc.
Now ,
Case 1 : when the brand slicer is selected : 1st and 2nd visual should show respective "BRAND" result
Case 2 : when the competitor brand slicer is selected : 1st and 3rd visual should show respective "COMPETITOR BRAND" result
Case 3 : if both brand and competitor brand slicer is selected : then 1st visual will show combined data ("BRAND" and "COMPETITOR BRAND" ), 2nd will show brand data and 3rd will show competitor data

The DAX measure I am using for 1st Visual is : 

CALCULATE(Sum(Dashboard_Data[Sales]),
USERELATIONSHIP(Dashboard_Data[Date],'New Date'[Date]),
FILTER(Dashboard_Data,
ISFILTERED(Dashboard_Data[Brand]) || ISFILTERED(Dashboard_Data[Competitor_brand]))

)

With above measure, I get the result only when either one of the slicer is selected (Brand or Competitor brand) but when I select both slicer then the visual comes as blank.

Any help would be appreciated here.

Thanks,
Ankita

[Anonymous · Accepted solution]

Hi @AnkitaaMishra ，

Create two calcualted table as the slicer table:

Brand = VALUES(Dashboard_Data[Brand])

Competitor = VALUES(Dashboard_Data[Competitor_Brand])

And then create the following measures to meet yout requirements:

First Sales Measure = 
VAR SelectedBrand = ISFILTERED(Brand[Brand])
VAR SelectedCompetitor = ISFILTERED(Competitor[Competitor_brand])

RETURN
SWITCH(
    TRUE(),
    SelectedBrand && NOT SelectedCompetitor,
    CALCULATE(SUM(Dashboard_Data[Sales]),
        USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
        FILTER(Dashboard_Data, Dashboard_Data[Brand] = SELECTEDVALUE(Brand[Brand]))
    ),
    
    SelectedCompetitor && NOT SelectedBrand,
    CALCULATE(SUM(Dashboard_Data[Sales]),
        USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
        FILTER(Dashboard_Data, Dashboard_Data[Competitor_brand] = SELECTEDVALUE(Competitor[Competitor_brand]))
    ),
    
    SelectedBrand && SelectedCompetitor,
    CALCULATE(SUM(Dashboard_Data[Sales]),
        USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
        FILTER(Dashboard_Data,
            Dashboard_Data[Brand] = SELECTEDVALUE(Brand[Brand]) &&
            Dashboard_Data[Competitor_brand] = SELECTEDVALUE(Competitor[Competitor_brand])
        )
    ),
    
    SUM(Dashboard_Data[Sales]) 
)

Second Sales Measure = 
VAR SelectedBrand = ISFILTERED(Brand[Brand])
VAR SelectedCompetitor = ISFILTERED(Competitor[Competitor_brand])

RETURN
SWITCH(TRUE(),
    SelectedBrand,
    CALCULATE(SUM(Dashboard_Data[Sales]),
        USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
        FILTER(Dashboard_Data, Dashboard_Data[Brand] = SELECTEDVALUE(Brand[Brand]))
    ),
    SelectedCompetitor,
    SUM(Dashboard_Data[Sales]),
    SUM(Dashboard_Data[Sales]))
    

Third Sales Measure = 
VAR SelectedBrand = ISFILTERED(Brand[Brand])
VAR SelectedCompetitor = ISFILTERED(Competitor[Competitor_brand])

RETURN
SWITCH(
    TRUE(),
    SelectedCompetitor ,
    CALCULATE(SUM(Dashboard_Data[Sales]),
        USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
        FILTER(Dashboard_Data, Dashboard_Data[Competitor_brand] = SELECTEDVALUE(Competitor[Competitor_brand]))
    ),
    
    SelectedBrand ,
    SUM(Dashboard_Data[Sales]),
    SUM(Dashboard_Data[Sales]) 
)
    
    

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @AnkitaaMishra , hello rajendraongole1, thank you for your prompt reply!

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[AnkitaaMishra]

Hi @Anonymous , No I am unable to resolve the issue yet. 

[rajendraongole1]

Hi @AnkitaaMishra - create below updated measure that should handle your three cases correctly below FYR

Combined Sales Measure =
VAR SelectedBrand = ISFILTERED(Dashboard_Data[Brand])
VAR SelectedCompetitor = ISFILTERED(Dashboard_Data[Competitor_brand])

RETURN
SWITCH(
TRUE(),
SelectedBrand && NOT SelectedCompetitor,
CALCULATE(SUM(Dashboard_Data[Sales]),
USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
FILTER(Dashboard_Data, Dashboard_Data[Brand] = SELECTEDVALUE(Dashboard_Data[Brand]))),

SelectedCompetitor && NOT SelectedBrand,
CALCULATE(SUM(Dashboard_Data[Sales]),
USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
FILTER(Dashboard_Data, Dashboard_Data[Competitor_brand] = SELECTEDVALUE(Dashboard_Data[Competitor_brand]))),

SelectedBrand && SelectedCompetitor,
CALCULATE(SUM(Dashboard_Data[Sales]),
USERELATIONSHIP(Dashboard_Data[Date], 'New Date'[Date]),
FILTER(Dashboard_Data, Dashboard_Data[Brand] = SELECTEDVALUE(Dashboard_Data[Brand]) ||
Dashboard_Data[Competitor_brand] = SELECTEDVALUE(Dashboard_Data[Competitor_brand]))),

SUM(Dashboard_Data[Sales]) -- Default case when no filter is selected
)

updated three conditions, please check the above, hope it works in your scenerio when you combine remaining slicers

[AnkitaaMishra]

Hi @rajendraongole1 , Thanks for your quick reply. 
I tried this solution but somehow its not working for my case.