Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count Distinct limitation in Group By functionality in Edit Query

 

power_bi_power_query_distinct_count_02.pngpower_bi_power_query_distinct_count_01.png

Would really appreciate some guidance on a trick issue I've run into with Group By in Query Editor on Power BI Desktop. I hope I can explain clearly.
I have a dataset in Power BI containing one row per instance of an event (a "fidelity check") relating to tutors on an educational program.
There are ~1,000 unique tutors (unique key is staff_site_sy), for whom there have been ~19,000 fidelity checks (ux is fidelity_check_id), thus ~19,000 rows in the table.
Other colums in this dataset are attributes of the tutors and checks (tutor name, date, score, observer, geography, etc.).
In my Power BI report, I need to be able to present tutor metrics, e.g., average score or total checks completed. I can easily do this using the dataset I have by creating Measures using DAX expressions (e.g., average or distinctcount).
The problem comes when I need to group tutors. For example, I have a metric related to average score (expressed as a %) on fidelity checks. I ultimately need to create visuals which show, for example, the number of tutors whose average score is 0-25%, 26-50%, 51-75%, etc. Binning and grouping in the Report View doesn't work for this, I don't think. I wish it did but obviously I need these groups to be dynamic/based on logic.
So my "go to" solution has always been to use Group By in the Query Editor, building a new dataset based of the one described but with one row per tutor. I group by staff_site_sy and can then create new columns in the Group By dialogue which use aggregates to create single average or count values per tutor. Then I can create a Conditional Column which groups tutors into ranges based on that new column.
This works great, but only when the required aggregate function is available in the Group By dialogue. When I select Sum, Average, Median, Min, and Max I can select a column to aggregate. But the option I need is Count Distinct. This function is not available. Not sure why...
Following extensive online searching, the only way I could find to attempt this is by adding Power Query code via the Advanced Editor option. I tried this and it seemed to work, but I don't trust it as I don't really know the Power Query/M language.
I've attached a screenshot of the code I added by hand in Advanced Edit - inserted to an existing Grouped Rows 'Applied Step'. The syntax doesn't throw error, although I do notice that I can no longer open the Applied Step via the right-hand Query Settings slide-in - obviously I'm breaking it by using a function which isn't available "out of the box".
Is there a better way to do this?! I cannot find any way of creating dynamic groups in the Power BI Report View. I believe my use case requires Conditional Columns and cannot be achieved using any of the available options in Report View (e.g., binning or grouping, I've even tried nested tables via the All Rows option but couldn't any sense of it or actually use it). Also would like to verify whether my code is actually right!
This kind of dynamic grouping is a key use case for us, so thanks in advance and look forward to hearing back!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anonymous,

Not really aboit what you mean of dynamic groups maybe instead of doing this on the query editor ypu can make a summarizes table on the report view using dax of a measure that calculates the values you need in combination with a table pf your groups could you provide a sample of data and expectes result.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Anonymous,

Not really aboit what you mean of dynamic groups maybe instead of doing this on the query editor ypu can make a summarizes table on the report view using dax of a measure that calculates the values you need in combination with a table pf your groups could you provide a sample of data and expectes result.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix, thank you so much for this solution!

 

I used New Table to create table in Report View with DISTINCT(staff_site_sy) as the key column. Then I created the required columns containing expressions to summarize/aggregate the data, making sure to use the CALCULATE prefix in those expressions and also relating this new table to main data table (using staff_site_sy). Everything seems to be working well.

 

I am happy I have been able to remove the table created using Group By in the Edit Query View and will stick to your method as far as possible in future. To get my column showing the range into which tutors fall, it was easy to write an IF statement like this:

 

Avg. pct fidelity range =
IF(tutor_metrics[Avg. pct fidelity] < .795, "1-79%",
IF(tutor_metrics[Avg. pct fidelity] < .855, "80-85%",
IF(tutor_metrics[Avg. pct fidelity] < .905, "86-90%",
IF(tutor_metrics[Avg. pct fidelity] < .955, "91-95%",
IF(tutor_metrics[Avg. pct fidelity] <= 1, "96-100%",
"N/A"))))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.