Forum Discussion

AnkitaaMishra's avatar
AnkitaaMishra
Super User
1 year ago
Solved

DAX Help

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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's avatar
    AnkitaaMishra
    Super User
    1 year ago

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