Forum Discussion
group by count distinct is grayed out
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'.
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.
3 Replies
- Vijay_A_Verma
Most Valuable Professional
Can you click on Add Grouping button and from the drop down, it should allow you to choose Case ID?
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.
