Calculate count of values

[dgolovanova]

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

[Greg_Deckler · Accepted solution]

@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

[Greg_Deckler · Accepted solution]

@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

[dgolovanova · Accepted solution]

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