DAX Measure to calculate the distinct feature counts across Manufacture & Products

1
votes

Am trying to build a DAX Measure that would compute distinct features of Manufacturer product based on Base Manufacturer.

Table Columns as below

Manufacturer Product Features
Honda Civic Feature1
Honda Civic Feature2
Honda Civic Feature3
Maruti Ciaz Feature1
Maruti Ciaz Feature4
Maruti Ciaz Feature5
Toyato Camry Feature2
Toyoto Camry Feature4
Toyoto Camry Feature6
Toyoto Camry Feature7

When a Pivot table is created with Manufacturer as each row and I would like to show the total features in one column and the distinct features in column 2.

Manufacturer - TotalCount - DistinctCountOverHonda
Honda - 3 - 3
Maruthi - 3 - 2 ( Feature4, Feature5) are the distinct features, when compared to Honda
Toyoto - 4 - 3 ( Feature4, Feature6, Feature7) are the unique features over Honda.

Please help me to write the measure for DistinctCountOverHonda & applying filter to remove the duplicate & keep just the unique features of Manufacturer present in Pivot table row.

In above example, I have kept Honda as a base for comparison over other manufacturers. Probably, I will add a slicer for a user to select the base manufacturer dynamically as parameter table and counts to be reflected based on user selection.

1
powerbidax

1 Answers

0
votes

Basically, you want to find all the distinct features of both manufacturers combined and then subtract out the distinct features for Honda.

DistinctCountOverBase =
VAR CurrentManufacturer =
    SELECTEDVALUE ( Table1[Manufacturer] )
VAR CombinedFeatures =
    CALCULATETABLE (
        DISTINCT ( Table1[Features] ),
        Table1[Manufacturer] IN { CurrentManufacturer, "Honda" }
    )
VAR HondaFeatures =
    CALCULATETABLE ( DISTINCT ( Table1[Features] ), Table1[Manufacturer] = "Honda" )
RETURN
    IF (
        CurrentManufacturer = "Honda",
        COUNTROWS ( HondaFeatures ),
        COUNTROWS ( CombinedFeatures ) - COUNTROWS ( HondaFeatures )
    )

Here's a slightly more generalized solution:

DistinctCountOverBase =

VAR Base = "Honda"

VAR CurrentManufacturer =
    VALUES ( Table1[Manufacturer] )

VAR CombinedFeatures =
    CALCULATETABLE (
        DISTINCT ( Table1[Features] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Manufacturer] IN CurrentManufacturer
                || Table1[Manufacturer] = Base
        )
    )

VAR BaseFeatures =
    CALCULATETABLE ( DISTINCT ( Table1[Features] ), Table1[Manufacturer] = Base )

RETURN

    IF (
        SELECTEDVALUE ( Table1[Manufacturer] ) = Base,
        COUNTROWS ( BaseFeatures ),
        COUNTROWS ( CombinedFeatures ) - COUNTROWS ( BaseFeatures )
    )