topic Re: Count based on multiple criteria DAX in DAX Commands and Tips

Count based on multiple criteria DAX

Burak83_ — Sat, 25 Mar 2023 00:27:25 GMT

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

Re: Count based on multiple criteria DAX

edhans — Sat, 25 Mar 2023 00:38:23 GMT

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

Re: Count based on multiple criteria DAX

FreemanZ — Sat, 25 Mar 2023 09:35:36 GMT

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?

Re: Count based on multiple criteria DAX

Burak83_ — Sun, 26 Mar 2023 10:37:17 GMT

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>)

Re: Count based on multiple criteria DAX

edhans — Mon, 27 Mar 2023 16:10:05 GMT

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)