Filter for distinct values

[James_Galis1]

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 that only contain services with CWB and HM only . All other ID's that have additional services to be filtered out

Date Service ID

2024-12-31   CWB       AC001637888
2024-12-31   HM         AC001637888
2024-12-03   CWB       AC006108798
2024-12-09   HM         AC006108798
2024-12-10   2HCAS    AC006108798
2024-12-13   2HCAS    AC006108798
2024-12-17   2HCAS    AC006108798
2024-12-24   2HCAS    AC006108798
2024-12-24   CCP         AC006108798
2024-12-31   HCPPA    AC006108798
2024-12-04   HM         AC004049560
2024-12-04   HCPPA    AC004049560
2024-12-09   HCPCCP  AC004049560
2024-12-12   HCPCCP  AC004049560
2024-12-12   PRC         AC004049560
2024-12-12   HCPDA    AC004049560
2024-12-18   HCPDA    AC004049560
2024-12-24   HCPDA    AC004049560
2024-12-30   CWB        AC004049560
2024-12-31   HCPDA    AC004049560
2024-12-01   HM         AC000000299
2024-12-03   HM         AC000000355
2024-12-05   CWB      AC000000355

The expected results from the above should show ID's AC001637888 and AC000000355 as the only ones that pass the condition

Thank you

@Ashish_Mathur 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[James_Galis1]

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 

[Anonymous · Accepted solution]

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

[James_Galis1]

Champion !
Thank you very much

[Anonymous]

Hi @James_Galis1 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Create a measure as below

Flag = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "@count1",
            CALCULATE (
                COUNT ( 'Table'[ID] ),
                FILTER (
                    ALLEXCEPT ( 'Table', 'Table'[ID] ),
                    'Table'[Service] IN { "CWB", "HM" }
                )
            ),
        "@count2", CALCULATE ( COUNT ( 'Table'[ID] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    )
VAR _id =
    SELECTEDVALUE ( 'Table'[ID] )
RETURN
    IF (
        _id
            = MAXX ( FILTER ( _tab, [@count1] = 2 && [@count2] = 2 ), [ID] ),
        1,
        0
    )

2. Create a table visual: put the field [ID] on it and add visual-level filter with the condition (Flag is 1)

Best Regards

[James_Galis1]

Hi

Thank you for your time looking into this

I have tried getting this to work but it does not quite seem to work when i enter into my model.
There are plenty more dates in the report im working with and it does not quite seem to filter down to how it's expected.

There is also ID's where it's missed ( example below )maybe due to multiple dates ?

Is there a way the solution would work with multiple dates as shown above but only to show services that have CWB and HM ?