topic Group by the results of a grouped table variable in DAX Commands and Tips

Group by the results of a grouped table variable

Power_BI_User_ — Tue, 21 Feb 2023 19:22:47 GMT

Hello,
I have a table, "prem_incs", of the following form:

premise_number	incident_year	job_id
100	2020	1
102	2020	2
104	2020	3
102	2020	4
106	2020	5
108	2020	6
106	2020	7
103	2020	8
105	2020	9
108	2020	10
102	2020	11
101	2020	12
105	2020	13
105	2020	14
109	2020	15
105	2020	16
103	2020	17
108	2020	18
107	2020	19
106	2020	20

I have created the following table variable, "first_grouping", which hopefully counts premise_number occurences by whatever year filter context is applied to it:

first_grouping = addcolumns( groupby( prem_incs, prem_incs[incident_year], prem_incs[premise_number] ), "prem_counts", calculate(count(prem_incs[job_id])) )

It produces a table like the following, which appears correct:

prem_counts	premise_number
1	100
3	102
1	104
3	106
3	108
2	103
4	105
1	101
1	109
1	107

However, when I try to then group this table a second time by prem_counts, I don't seem to be able to achieve the result I'm seeking.

Here's the dax code that I've attempted:

overall_grouping = var first_grouping = addcolumns( groupby( prem_incs, prem_incs[incident_year], prem_incs[premise_number] ), "prem_counts", calculate(count(prem_incs[job_id])) ) return addcolumns( groupby( first_grouping, [prem_counts] ), "bins", calculate(countx(first_grouping, [premise_number])) )

Here's the result the dax returns:

prem_counts	bins
1	10
2	10
3	10
4	10

And here's the result I desire:

prem_counts	bins
1	5
2	3
3	1
4	1

What should I do differently to be able to perform this grouping? My goal is to ultimately to be able to use filters remove job_id values from consideration and then to dynamically recalculate the bin numbers per year for visuals.

Any and all help is appreciated; thanks!

(My goal for the output visual is someting like the following):

Re: Group by the results of a grouped table variable

amitchandak — Wed, 22 Feb 2023 04:07:33 GMT

Power_BI_User_ , You should use current group by

example

return
groupby(
first_grouping,
[prem_counts],
"bins", calculate(countx(CURRENTGROUP(), [premise_number]))
)

refer for options

https://medium.com/@amitchandak/power-bi-power-query-vs-dax-append-and-summarize-data-233f173d0839

Re: Group by the results of a grouped table variable

Jihwan_Kim — Wed, 22 Feb 2023 04:16:29 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = VAR _firstgroup = ADDCOLUMNS ( SUMMARIZE ( prem_incs, prem_incs[incident_year], prem_incs[premise_number] ), "@premise_count", CALCULATE ( DISTINCTCOUNT ( prem_incs[job_id] ) ) ) VAR _secondgroup = GROUPBY ( _firstgroup, prem_incs[incident_year], [@premise_count], "@countbins", SUMX ( CURRENTGROUP (), 1 ) ) RETURN _secondgroup

Re: Group by the results of a grouped table variable

Power_BI_User_ — Wed, 22 Feb 2023 12:12:53 GMT

This solution worked perfectly for me - thank you for your help!