CODEWITHSUNDEEP
powerbidax
WTWT
WTWT

Reputation: 15

PowerBI DAX - countdistinct based on two columns

I would like to calculate column 'E' in DAX

  1. I wanted to count distinct 'B' based on 'C' and filter on 'D' = instock.

  2. I later want to sum column E -- what is the best way to do this to exclude the duplications

enter image description here

Thanks in advance

Upvotes: 0

Views: 92

Answers (2)

Ozan Sen
Ozan Sen

Reputation: 2615

Your question is split into 2 parts: Go step by step then:

First Step Code:

Result =
CALCULATE (
    DISTINCTCOUNT ( 'Product'[size] ),
    ALLEXCEPT ( 'Product', 'Product'[product code] ),
    'Product'[stock] = "instock"
)

First

Second Step:

Result =
VAR TblSummary =
    ADDCOLUMNS (
        VALUES ( 'Product'[product code] ),
        "DISCTotal",
            CALCULATE (
                DISTINCTCOUNT ( 'Product'[size] ),
                ALLEXCEPT ( 'Product', 'Product'[product code] ),
                'Product'[stock] = "instock"
            )
    )
RETURN
    SUMX ( TblSummary, [DISCTotal] )

Second_Step

Upvotes: 0

Jos Woolley
Jos Woolley

Reputation: 9062

You mean you want a Calculated Column?

=
VAR ThisProductCode = 'Table'[product code]
RETURN
    CALCULATE(
        DISTINCTCOUNT( 'Table'[size] ),
        FILTER(
            'Table',
            'Table'[product code] = ThisProductCode
                && 'Table'[stock] = "instock"
        )
    )

Not sure what you mean by your second request, though.

Upvotes: 1

Related Questions