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 ,

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

Champion !
Thank you very much