How to show values against only categories that were filtered

[gmrram]

Hi,

I have products table (Dim) with column "Color" connected to  Sales table (fact). with the below DAX I was able to count rows for black and red and same is reflecting against all the colors, but I want to relect the count only against red and black (14,792) and for all other colors it should reflect blank. 

# Black and Red =

CALCULATE(

    COUNT(Sales[OrderDate]),

    FILTER(ALL(Products), Products[Color] = "Black" || Products[Color] = "Red"))

Could you please help me to solve this. Thank you.

[Idrissshatila · Accepted solution]

Hello @gmrram ,

try the following code

# Black and Red = 
var sales = CALCULATE(
    COUNT(Sales[OrderDate]),
    FILTER(ALL(Products), Products[Color] = "Black" || Products[Color] = "Red"))
    return
    IF(SELECTEDVALUE(Products[Color]) = "Black" ||SELECTEDVALUE(Products[Color]) = "Black",sales,BLANK())

[Jonvoge]

Hi gmrram

You can create an additional CAlculated Measure as follows, and use that in your table instead. It uses the SWITCH() formula to only display the result of your initial measure, when the column name is "Red" or "Black":

Switch(Products[Color],

"Red", [# Black and Red],

"Black", [# Black and Red],

Blank()

)

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.

[gmrram]

Hi @Jonvoge , Thanks for your reply. It seems your suggested solution is not working as the first argument in Swith function should be expression. Please let me know if I'm missing anyting. However resolved using Selected Value as suggested by Idrissshatila. Thanks again.