Count based on multiple criteria DAX

[Burak83_]

Hi Gents,

I would like to count distinct values based on the combination of multiple columns.(In sample Location+Item+Status) I added my table as data model to count distinct values but it does not give correct results. I know how to distinct count using helper column or excel function like =IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)but is there any way to do it in power pivot ? as calculated column or measure? Thanks for your help and comments.

Location	Item	Status	Distinct Count
Kitchen	Plates	Moved	1​
Kitchen	Spoons	Stayed	1​
Saloon	TV	Moved	1​
Saloon	Chair	Moved	2​
Saloon	Chair	Moved	2​
Bathroom	Mirror	Stayed	1​

[FreemanZ]

hi @Burak83_ 

Try to plot a table visual with columns:Location, Item, Status, and a measure like:

Distinct Count = COUNTROWS(TableName)

in case of issue, could you provide some sample data?

[edhans]

This code does it. 

Distinct Count 2 = 
VAR varItems = SELECTEDVALUE('Table'[Item])
VAR varLocation = SELECTEDVALUE('Table'[Location])
VAR varStatus = SELECTEDVALUE('Table'[Status])
VAR varFilteredTable =
        CALCULATETABLE(
            'Table',
            'Table'[Item] = varItems
                && 'Table'[Location] = varLocation
                && 'Table'[Status] = varStatus
        )
VAR Result = COUNTROWS(varFilteredTable)
RETURN 
    Result

[Burak83_]

Hi Edhans,

Thanks for the solution. However, since I am using powerpivot it does not support selectedvalue. Is there any other equivalent formula that I can use? like IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)

[edhans]

Yes. Just use this instead of SELECTEDVALUE() in PowerPivot. It gives the same results.

IF ( HASONEVALUE( <columnName> ), VALUES( <columnName> ), <alternateResult> )

The first one would be:

IF ( HASONEVALUE( Table[Item] ), VALUES( Table[Item] ) )

Note that SELECTEDVALUE is coming to PowerPivot. See New DAX Functions in Excel Data Models and Power Pivot (office.com)