Help wanted: aggregate distinct count based on 4 groupby criteria.

[Anonymous]

Hi Everyone,

I am trying to get a distinct count of patient visits grouped by 4 criteria (grouper, month, financial class, financial ID) so that I can calculate revenue projection. However, my formula to get that distinct count does not work as GROUPBY METHOD cannot be used together with DISTINTCTCOUNT method. Please see my formula below. Is there a way to get around? Or Do I need to build a custom function? I thought PowerBI has that build-in function but it does not appear so? Any suggestions are appreciated. 

Formula:

Distinct Count Grouped By 4 Criteria = GROUPBY('TABLE1','TABLE1'[FISCAL_MONTH],'TABLE1'[Grouper],'TABLE1'[FINANCIAL_CLASS_ID],'TABLE1'[FINANCIAL_CLASS],"Grouped Unfiltered Count",DISTINCTCOUNT(CURRENTGROUP(),COUNTROWS(SELECTCOLUMNS('TABLE1', [PATIENT_VISIT ]))))

There are other formulas needed to get to the final expected output, but I am not including them to confuse readers.

Dummy Data Source is on the Data tab.

PATIENT_VISIT	Month_Name	Cal_Yr	FISCAL_YEAR	Grouper	CATEGORY	TOT_CHGS	TOT_PMTS	TOT_INS_PMTS	TOT_PAT_PMTS	TOT_ADJ	TOT_INS_ADJ	TOT_PAT_ADJ	TOT_ACCT_BAL	FINANCIAL CLASS	FINANCIAL ID
1	July	2021	2022	3M	1	452	0	0	0	-452	-452	0	0	2	A
2	April	2022	2022	3M	1	452	0	0	0	-452	-452	0	0	4	B
3	July	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	5	C
4	July	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	102	D
5	July	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	105	E
6	July	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	106	F
1	July	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	107	G
1	July	2021	2022	3D	3	452	0	0	0	-452	-452	0	0	2	H
1	December	2021	2022	3M	3	452	0	0	0	-452	-452	0	0	4	I
2	July	2021	2022	3M	3	452	0	0	0	-452	-452	0	0	12	J
22	October	2021	2022	3M	2	452	0	0	0	-452	-452	0	0	102	J
22	September	2021	2022	3M	4	452	0	0	0	-452	-452	0	0	105	K

Expected Output is on the Expected Output tab. 

CATEGORY	FINANCE ID	FINANCE CLASS	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	TOTAL
1	2	A	-219386	-219386	-232926	-223778	-219386	-217008	-217282	-204108	-236311	-206761	-189104
1	4	B	-260.125	-227.61	-265.545	-265.545	-373.93	-319.737	-314.318	-276.383	-373.93	-281.802	-325.157
1	5	C	0	0	0	-392.86	0	-196.43	0	0	0	0	0
2	106	M	-2390.8	-2091.95	-2988.5	-2988.5	-3586.19	-2988.5	-1793.1	-2390.8	-1494.25	-2689.65	-2988.5
2	107	N	-39454.1	-45372.2	-50194.4	-46249	-50852	-48440.9	-48440.9	-37481.4	-52824.7	-49536.8	-54578.2
3	2	A	-322826	-324751	-328216	-329756	-306271	-280091	-305886	-293951	-338996	-299341	-257568
3	102	J	0	0	0	0	0	0	0	0	0	0	0
4	2	C	-817166	-890173	-886227	-832106	-825623	-784469	-773757	-738804	-888482	-782495	-697086
4	4	C	-27386.5	-27190.2	-28368.1	-23263.8	-22674.9	-19828.3	-22380.4	-19141.1	-22085.9	-19730.1	-17766.9
4	5	N	-2601.8	-3469.06	-2698.16	-2312.71	-2794.52	-1830.9	-1445.44	-2409.07	-2698.16	-2505.44	-1252.72

[Ashish_Mathur · Accepted solution]

Hi,

I do not understand how distinctcount of Patient ID's can be negative number (see your innitial post).  You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur thank you! This does calculate the distinct count of visitor by multiple groupby criteria. Curious how did you come up with this solution? The calendar table is created as a dimension for filtering purpose? 

[Ashish_Mathur]

You are welcome.  Just learnt it myself.  If my previous reply helped, please mark that reply as Answer.

[Anonymous]

I would probably just use REMOVEFILTERS + VALUES (a very common pattern in DAX).
For instance within the Contoso model let's suppose we want to count distinct products by Category + Country pair. By using the basic measure:

DC_Basic = DISTINCTCOUNT ( 'Sales'[ProductKey] )

You will get correct results only if the Category and Country are the only columns in your visual. One additional issue might be that your total will show +1 as compared to the expected number. If I remember correclty this is happening because of the blank row feature in DAX. There are values in the dimension that do not exist in the Fact table (Sales in Contoso), and DAX adds one blank row to account for these. Some functions take into account this hidden blank row and some don't.

To avoid this issue you can filter by the Sales table:

DC_Basic_Calculate = 
CALCULATE (
    DISTINCTCOUNT ( 'Product'[ProductKey] ),
    'Sales'
)

However, in order to obtain a correct count regardless of the columns used in the visual you need to alter the filter context that the visual is creating (for instance in the table on the right where I added the SubCategory column).

You can use REMOVEFILTERS or ALL (the former is a new function used as an alias for the latter, but it is only used as a CALCULATE modifier and does not return a table as ALL does).

As you can see, the third measure returns the correct counts even for arbitrary visuals.

DC_REMOVEFILTERS = 
VAR Categories = VALUES ( ProductCategory[ProductCategoryKey] )
VAR Countries = VALUES ( Geography[RegionCountryName] )

RETURN
CALCULATE (
    DISTINCTCOUNT( 'Sales'[ProductKey] ),
    REMOVEFILTERS ( 'Product' ),
    REMOVEFILTERS ( Geography[RegionCountryName] ),
    Categories,
    Countries
)

You may notice that in the second measure I count over the Product table (best practice) while in the 1st and 3rd - over the Fact. In this case I'm doing that because the two columns I have to count by are from two different dimensions and the filters do not propagate from one another, but they do "meet" at Sales level. Having bi-directional relationships that would progagate the filters across dims would allow to count again over the Product, but introducing bi-directional relationships would be even a worse alternative than counting over the Fact.

One more point. in the last measure I am removing filters from the whole Product table but only from 

Geography[RegionCountryName] column. That is because I'm dragging into the table on the right the Subcategory and have to remove the filter either from both Category/Subcategory tables used in the visual or the Product table for which the extended table contains the Category and Subcategory, or shorter (but not necessarily better) just from the Fact table (where the extended table contains the whole model).

[Anonymous]

If your visual is at the granularity of the four columns that you want the DISTINCTCOUNT by, then you don't need to write any complex dax, just write a basic DISTINCTCOUNT ( Col ) and drag it in the table/matrix next to the four columns. The result should be as if you computed the values in a SQL statement with GROUP BY the 4 fields. This is because Power BI will automatically create for you a filter context based on the fields you used in the visual (+ slicers and the filter pane).

If you need the result to be computed independently of the visuals granularity it is used in, then you have to use functions like SUMMARIZE or SUMMARIZECOLUMNS (or maybe even GROUPBY, but it is used very rarely when authoring measures) and compute the DISTINCTCOUNT() at that custom granularity irrespective of the visuals.

[Anonymous]

Hi, @Anonymous , thank you so much for getting back to me!  

I did initially use the suggested method (calculate(distinctcount()) to get a distinct count of patient visit. I need to use that value to multiply other 2 aggregate values to calculate revenue project, which does not work in PowerBI. That's why I am trying the groupby method. If you are using summarize()/groupby(), how would you go about calculating that distinct value that can be filtered by named 4 criteria? Thanks again.