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

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: