topic Re: Distinct count excluding blank rows in a matrix in DAX Commands and Tips

Distinct count excluding blank rows in a matrix

menik_sun — Wed, 05 May 2021 10:05:27 GMT

Chart is like below

Contract Type	Nos	Designation	Location
Permanent	1	Manager 1	London
Permanent	1	Manager 1	Paris
Permanent	1	Manager 1	New York
Permanent	2	Manager 1	Brussels
Permanent	2	Manager 1	Amsterdam
Permanent	3	Worker 1	Washington DC
Permanent	3	Worker 1	London
Permanent	3	Worker 1	Paris
Permanent	3	Worker 1	Brussels
Contract		Worker 2	Amsterdam
Contract		Worker 3	New York
Contract		Worker 4	London
Contract		Worker 5	New York
Contract		Worker 5	Washington DC

First Formula Required: Count of Nos
Distinct Count of No excluding Blanks
for above chart, outcome should be 3 (unique count of 1, 2 & 3)

Second Formula Required: Count of Designation
Distint Count of Designation
for above chart, outcome should be 6

At the end, if I can get a table like below, it would be of great help:

Contract Type	Count of Nos	Count of Designation
Permanent	3	2
Contract	4	4
Total	7	6

Re: Distinct count excluding blank rows in a matrix

amitchandak — Wed, 05 May 2021 11:21:00 GMT

menik_sun , You can try measure like

three examples

calculate(distinctcount(Table[Nos]), filter(Table, not(isblank([Nos]))))

calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Nos]))))

calculate(distinctcount(Table[Designation]), filter(Table, not(isblank([Designation]))))

Re: Distinct count excluding blank rows in a matrix

menik_sun — Thu, 06 May 2021 07:15:26 GMT

Thanks for your reply amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.

Below is what I get after using the formula:

Thanks for your reply @amitchandak . It's showing correct total but not while using field dimension. It is showing total result even after using field as rows.

Below is what I get after using the formula:

With suggested formula Formula required for
Contract Type Count of Nos Count of Nos
Permanent 7 3
Contract 7 4
Total 7 7

Re: Distinct count excluding blank rows in a matrix

Anonymous — Fri, 07 May 2021 03:27:35 GMT

Hi menik_sun ,

Please try the following measures:

first formula - COUNTROWS(SUMMARIZE(Table, Table[ContractType], Table[Desgination], Table[Nos]))

second formula - COUNTROWS(SUMMARIZE(Table, Table[ContractType], Table[Designation]))

I arrived at the following matrix using ContractType in Rows.

Cheers