Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I am trying to group all my 'Create Date/Time' and 'Close Date/Time' and have a column that counts a distinct 'Case ID' . This is for a dual axis line graph (based on the dates) that counts a distinct 'Case ID'. Im trying to use the group by function in Power Query but the column is grayed out and wont let me select 'Case ID'.
Solved! Go to Solution.
Hi @sandersonmm ,
Count Distinct Rows – Displays the distinct number of rows in a grouping.
When the Group by function selects Count-related operations, Column will not display columns, but will directly affect all rows. When operations such as SUM, AVG, etc. are selected, they will be displayed.
https://docs.microsoft.com/en-us/power-query/group-by
You can also do it with the dax function:
Main table:
Create calculated table.
Table 2 =
SUMMARIZE(
'Table','Table'[Created Date/Time],'Table'[Closed Date/Time],"ID",DISTINCTCOUNT('Table'[Case ID]))
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ignore the greyed out dropdown. Just click ok.
Hi @sandersonmm ,
Count Distinct Rows – Displays the distinct number of rows in a grouping.
When the Group by function selects Count-related operations, Column will not display columns, but will directly affect all rows. When operations such as SUM, AVG, etc. are selected, they will be displayed.
https://docs.microsoft.com/en-us/power-query/group-by
You can also do it with the dax function:
Main table:
Create calculated table.
Table 2 =
SUMMARIZE(
'Table','Table'[Created Date/Time],'Table'[Closed Date/Time],"ID",DISTINCTCOUNT('Table'[Case ID]))
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you click on Add Grouping button and from the drop down, it should allow you to choose Case ID?
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
52 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |