advanced filter functon - filter based on array

[Anonymous]

hello every one and happy new year,

Im trying to make a measure , using tthe filter function. I have wrote the dax code below, but it isnot working.

i want to count the IncidentId base on casecategoryid and casestatusid. when i im trying to exclude suply ids the measure doesnt work.

Thanks in advance

var el_id=

SUMMARIZE(

FILTER(

    'CRM IncidentBase',

    'CRM IncidentBase'[core_casestatusid] = "540de4c9-ddb6-e611-80bc-00155d00de2c"

    &&

    'CRM IncidentBase'[core_casecategoryid] = "1f7bf439-a091-e911-a839-005056a82c5e"

),

[supplyid]

)

return

CALCULATE(

    COUNT(

        'CRM IncidentBase'[IncidentId]

    ),

    FILTER(

        'CRM IncidentBase',

        'CRM IncidentBase'[core_casestatusid]="540de4c9-ddb6-e611-80bc-00155d00de2c"

        &&

        (

        'CRM IncidentBase'[core_casecategoryid]="57a8433a-f5a8-e711-80d2-005056a86dbb"

        ||

        'CRM IncidentBase'[core_casecategoryid]="0302d5e7-5ea4-e711-80d2-005056a86dbb"

        ||

        'CRM IncidentBase'[core_casecategoryid]="53c73da2-283f-e911-a837-005056a82c5e"

        )

        &&

        not ('CRM IncidentBase'[supplyid]) in {el_id}

        )

    )

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please update the formula of your measure as below and check if it can work....

Measure = =
VAR el_id =
    CALCULATETABLE (
        VALUES ( 'CRM IncidentBase'[supplyid] ),
        FILTER (
            'CRM IncidentBase',
            'CRM IncidentBase'[core_casestatusid] = "540de4c9-ddb6-e611-80bc-00155d00de2c"
                && 'CRM IncidentBase'[core_casecategoryid] = "1f7bf439-a091-e911-a839-005056a82c5e"
        )
    )
RETURN
    CALCULATE (
        COUNT ( 'CRM IncidentBase'[IncidentId] ),
        FILTER (
            'CRM IncidentBase',
            'CRM IncidentBase'[core_casestatusid] = "540de4c9-ddb6-e611-80bc-00155d00de2c"
                && 'CRM IncidentBase'[core_casecategoryid]
                IN {
                "57a8433a-f5a8-e711-80d2-005056a86dbb",
                "0302d5e7-5ea4-e711-80d2-005056a86dbb",
                "53c73da2-283f-e911-a837-005056a82c5e"
            }
                && NOT ( 'CRM IncidentBase'[supplyid] IN { el_id } )
        )
    )

If the above one can't help you get the desired result, please provide some raw data in the table 'CRM IncidentBase' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards