topic Re: Calculate count of values in DAX Commands and Tips

Calculate count of values

dgolovanova — Tue, 03 Oct 2023 20:44:59 GMT

Good afternoon,
please help me to write a formula on DAX or solve a problem using Power Query.
I have a table with two columns, CLIENT and FUND (table below).

One client can participate in several funds.

I need to calculate two measures:
1. Count of clients who participate ONLY in the "IRF" fund and not in any other funds.
2. Count of clients who participate ONLY in "IRF" or "DYN" funds.
Thank you in advance.

Regards

CLIENT	FUND
1000004	IRF
1000004	DYN
1000010	IRF
1000010	MON
1000015	IRF
1000015	Other
1000015	MON
1000018	IRF
1000018	Other
1000018	MON
1000034	IRF
1000034	Other
1000065	IRF
1000065	DYN
1000072	IRF
1000084	IRF
1000084	Other
1000084	MON
1000170	IRF
1000170	MON
1000253	IRF
1000253	DYN
1000261	IRF
1000559	IRF
1000559	DYN
1000559	Other

Re: Calculate count of values

Greg_Deckler — Tue, 03 Oct 2023 21:29:46 GMT

dgolovanova Try these measures, both return a 1 when the condition is met and 0 otherwise. You can use these as filters on your table or visual. PBIX is attached below signature:

Measure IRF Only = VAR __Fund = "IRF" VAR __Table = SUMMARIZE( 'Table', [CLIENT], "__Count", COUNTROWS('Table'), "__Funds", CONCATENATEX('Table', [FUND], "|" ) ) VAR __Result = IF( MAXX( __Table, [__Count]) = 1 && PATHCONTAINS(MAXX( __Table, [__Funds]), __Fund), 1, 0) RETURN __Result Measure IRF and DYN Only = VAR __Funds = { "IRF", "DYN" } VAR __BadFunds = DISTINCT(SELECTCOLUMNS(FILTER('Table', NOT( [FUND] IN __Funds ) ), "__Fund", [FUND] ) ) VAR __BadClient = COUNTROWS( SELECTCOLUMNS( FILTER( 'Table', [FUND] IN __BadFunds), "__Client", [CLIENT] ) ) VAR __Result = IF( __BadClient > 0, 0, 1) RETURN __Result

Re: Calculate count of values

dgolovanova — Tue, 03 Oct 2023 22:44:27 GMT

Thank you so much for your quick response and help.
Everything works!
All the best 😉