DAX - Calculate counts by using filter on multible columns

Question

I am trying to get distinct counts by filters on tabular. Scenaria is like below.

ID ¦ProductName ¦ color ----------¦ ------------------ ¦------ 1 ¦Crankarm ¦ NA 2 ¦Bearing Ball ¦ Black 1 ¦Bearing Ball ¦ Black 3 ¦Bearing Ball ¦ Black 4 ¦Blade ¦ Black 2 ¦Crankarm ¦ Black 5 ¦Bearing Ball ¦ Black 6 ¦Bearing Ball ¦ Black 6 ¦Blade ¦ Black 7 ¦Blade ¦ Black 7 ¦Blade ¦ Black 3 ¦Blade ¦ Black 5 ¦Crankarm ¦ Black 9 ¦Decal ¦ NA

I want to get 3 different Distinct Counts of 'ID'. Filters are like below: 1. ProductName ='Bearing Ball' and Color = 'Black' >(ID=2,1,3,5,6 > Distinct count:5) 2. ProductName ='Blade' and Color = 'Black' >(ID=4,6,7,7,3 > Distinct count:4) 3. ProductName ='Crankarm' and Color = 'Black' >(ID=2,5 > Distinct count:2) And will sum all 3 distinct counts. Final result of my measure will be: 5+4+2 = 11.

I calculated measures by below code for all 3 products separetly. And SUM result of 3 measures in a other measure.(FinalResult:= [BB]+[BL]+[CR] ) but it didn't work.

BB:= CALCULATE ( DISTINCTCOUNT ( Product[ID] ), FILTER ( VALUES (Product ), Product[ProductName] = "Bearing Ball" && Product[Color] = "Black" ) ) BL:= CALCULATE ( DISTINCTCOUNT ( Product[ID] ), FILTER ( VALUES (Product ), Product[ProductName] = "Blade" && Product[Color] = "Black" ) )

CR:= CALCULATE ( DISTINCTCOUNT ( Product[ID] ), FILTER ( VALUES (Product ), Product[ProductName] = "Crankarm" && Product[Color] = "Black" ) )

I can't get distinct counts in same query by adding OR because, different ProductName can have same ID. And if use in same query, i will lost them.

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-07T19:54:21

One easy way is create a calculated column like this:

IDNameColor = [ID] &"-"& [ProductName]  & "-" & [Color]

Then use that calculated column in a measure like this to get the count of products:

Total Count :=
CALCULATE (
    DISTINCTCOUNT ( 'Product'[IDNameColor] ),
    FILTER (
        ALL ( 'Product' ),
        ( [Color] = "Black"
            && [ProductName] = "Bearing Ball" )
            || ( [Color] = "Black"
            && [ProductName] = "Blade" )
            || ( [ProductName] = "Crankarm"
            && [Color] = "Black" )
    )
)

You can remove the ALL('Product') function if you want it to be affected by filters.

Aleternatively if you don't want to create a calculated column the measure can be like this:

Total Count :=
COUNTAX (
    FILTER (
        DISTINCT ( 'Product' ),
        ( [Color] = "Black"
            && [ProductName] = "Bearing Ball" )
            || ( [Color] = "Black"
            && [ProductName] = "Blade" )
            || ( [ProductName] = "Crankarm"
            && [Color] = "Black" )
    ),
    [ID] & [ProductName]
        & [Color]
)

DAX - Calculate counts by using filter on multible columns

1 Answers