Jonathan has attended:
Power BI Modelling, Visualisation and Publishing course
Count Distinct
ID Which documents were missing?
18
18
17
17 Slip / Endorsement
16
16
10
10
9
9
9 Placement Checklist
9 Placement Checklist
9 Slip / Endorsement
9 Slip / Endorsement
8
8 Slip / Endorsement
Expected Value
Slip / Endorsement 3
Placement Checklist 1
I have the above table where I need to count the number of missing documents for the ID. Can someone help with a suitable DAX command?
RE: Count Distinct
Hi Jonathan,
Thank you for the forum question.
I do not know your table name, so I just called it table below.
CountMissingDocs = COUNTROWS(GROUPBY(Table,Table[ID],Table1[Which documents were missing?]))
If you put this to a matrix visual and filter [Which documents were missing?] to not show blanks. Power BI like Excel hates blanks.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Count Distinct
Thank you for the DAX statement. Unfortunately, it didn't do as I expected.
I am particularly looking for the distinct count of the same type of missing doc within an ID.
I see that you use GROUPBY to identify the same rows of ID but its still not giving me the distinct row counts.
E.g.
ID Document Type
9 Type1
9
9 Type2
9
9 Type1
9 Type1
9
9
I would expect the response of Type1 = 1, Type2 = 1
With blanks being ignored.
Is there anything else I could try?
RE: Count Distinct
This was the original table....
ID Which documents were missing?
18
18
17
17 Slip / Endorsement
16
16
10
10
9
9
9 Placement Checklist
9 Placement Checklist
9 Slip / Endorsement
9 Slip / Endorsement
8
8 Slip / Endorsement
With Expected Values as
Slip / Endorsement 3 (1 for 17 ID, 1 for 9 ID, 1 for 8 ID, Total = 3)
Placement Checklist 1 (1 for 9 ID, Total = 1)
RE: Count Distinct
Hi Jonathan,
Please find attached Power BI file.
My DAX return the same result as you expect.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: Count Distinct
Thank you Jens. I will take a look.
RE: Count Distinct
Hi Jens, is it possible to only include non blank rows in the dax formula rather than using the filter?
I have tried using different things but the syntax for GroupBy seems to only want column names as arguments.
RE: Count Distinct
Hi Jonathan,
Try:
test = CALCULATE(COUNTROWS(GROUPBY('Table','Table'[ID],'Table'[ID Which documents were missing?])),filter('Table',[ID Which documents were missing?]<>""))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
