Forum Discussion

James_Galis1's avatar
James_Galis1
Helper II
1 year ago
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 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 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      James_Galis1
      Helper II
      1 year ago

      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 ?

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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