group by count distinct is grayed out

[sandersonmm]

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'.

[strongCoreZach]

ignore the greyed out dropdown. Just click ok. 

[v-yangliu-msft · Accepted 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.

[Vijay_A_Verma]

Can you click on Add Grouping button and from the drop down, it should allow you to choose Case ID?