Forum Discussion

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 : 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

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:

Best regards,

Joyce

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

DAX Help.pbix53 KB

5 Replies

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
  Super User
  1 year ago
  Hi rajendraongole1 , Thanks for your quick reply.
  I tried this solution but somehow its not working for my case.
Anonymous
Not applicable
1 year ago
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
  Super User
  1 year ago
  Hi Anonymous , No I am unable to resolve the issue yet.

Anonymous

Not applicable

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.

DAX Help.pbix53 KB

Forum Discussion

DAX Help

5 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX