Forum Discussion
Solved
Filter for distinct values
Hi All Im requiring assistance in the following. I have a table with an employee id and a list of services assosciated with that ID. What i am looking at acheiving is filtering for ID's ...
Hi James_Galis1 ,
Please update the formula of measure as below and check if it can return the correct result...
Flag = VAR _tab = SUMMARIZE ( 'Table', 'Table'[ID], "@count1", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Service] IN { "CWB", "HM" } ) ), "@count2", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) ) VAR _id = SELECTEDVALUE ( 'Table'[ID] ) RETURN IF ( _id = MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ), 1, 0 )Best Regards
I have also included the data source im working with just in case.
Note: I did get it working for 1 ID ( AC12345 ) which i entered manually on the data sheet for testing the solution
https://docs.google.com/spreadsheets/d/1cFwE9sN8HZwQZplr3aYhvxcVQlBm0ATo1VtAaKqz-dA/edit?usp=sharing
Hi James_Galis1 ,
Please update the formula of measure as below and check if it can return the correct result...
Flag = VAR _tab = SUMMARIZE ( 'Table', 'Table'[ID], "@count1", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Service] IN { "CWB", "HM" } ) ), "@count2", CALCULATE ( DISTINCTCOUNT ( 'Table'[Service] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ) ) VAR _id = SELECTEDVALUE ( 'Table'[ID] ) RETURN IF ( _id = MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ), 1, 0 )Best Regards
Champion !
Thank you very much