Forum Discussion
Solved
Calculate count of values
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...
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 __ResultThank you so much for your quick response and help.
Everything works!
All the best 😉
Greg_Deckler
Community Champion
Community Championdgolovanova 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
__ResultThank you so much for your quick response and help.
Everything works!
All the best 😉