Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
James_Galis1
Helper II
Helper II

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 

1 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
    )

vyiruanmsft_0-1737016550213.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1737078955576.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
James_Galis1
Helper II
Helper II

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
    )

vyiruanmsft_0-1737016550213.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Champion !
Thank you very much

v-yiruan-msft
Community Support
Community Support

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)

vyiruanmsft_0-1736992373340.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ?

James_Galis1_0-1737004942526.png

 

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

 

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.