cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## How to show values against only categories that were filtered

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"))

1 ACCEPTED SOLUTION
Super User

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())``````

 Vote for my Community Mobile App Idea

Proud to be a Super User!

3 REPLIES 3
Super User

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())``````

 Vote for my Community Mobile App Idea

Proud to be a Super User!

Solution Supplier

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.

Regular Visitor

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.