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 ...
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

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 ?