98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Count Distinct
Count Distinct
Resolved · Low Priority · Version 365
Mon 28 Mar 2022, 13:24Delegate Jonathan said...
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?
Mon 28 Mar 2022, 16:40Trainer Jens said...
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
Mon 28 Mar 2022, 17:16
Edited on Mon 28 Mar 2022, 17:24Delegate Jonathan said...
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?
Mon 28 Mar 2022, 17:29Delegate Jonathan said...
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)
Mon 28 Mar 2022, 17:40Trainer Jens said...
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...
Tue 29 Mar 2022, 08:50
Edited on Tue 29 Mar 2022, 08:50Delegate Jonathan said...
RE: Count Distinct
Thank you Jens. I will take a look.
Tue 29 Mar 2022, 16:26
Edited on Tue 29 Mar 2022, 16:27Delegate Jonathan said...
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.
Tue 29 Mar 2022, 17:19Trainer Jens said...
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
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Data Cleaning and TransformationEnsure your data is clean and well-structured before importing it into Power BI. Use Power Query Editor to clean and transform your data. This tool allows you to perform various operations like filtering, merging, and reshaping data, ensuring it's in the right format for analysis. |
