Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

 

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

 

image.png

 

1 ACCEPTED SOLUTION
Idrissshatila
Super User
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())


Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Idrissshatila
Super User
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())


Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Jonvoge
Solution Supplier
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.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.