Create a measure by filtering data from 2 tables

[hemanth2018]

Hi,

I have 2 tables Incident and Project where incidentID column is the commom column from both the tables.

Incident table has statuses ( Rejected and approved)

Project table has statuses ( Not Started and In Progress)

now i need to create a measure like  - get the count of incidents with status  = Rejected from incidents and count of incidents in project with status in Not Started and In Progres.

condition is - we need to get the count for theincidents whose status is rejected and for the same incidents in project with status Not Started and In Progres.

Measure  = Rejected & Not Started & In Progres

Need help ASAP.

[v-shex-msft · Accepted solution]

HI @hemanth2018,

You can try to use below formula if it works for your scenario.

Measure =
VAR idList =
    CALCULATE (
        VALUES ( Incident[incidentID] ),
        FILTER ( ALLSELECTED ( Incident ), Incident[statuses] = "Rejected" ),
        VALUES ( Incident[incidentID] )
    )
RETURN
    CALCULATE (
        COUNT ( Product[incidentID] ),
        FILTER (
            ALL ( Project ),
            Project[incidentID] IN idList
                && Project[statuses] IN { "Not Started", "In Progres" }
        )
    )

If above not help, please share same sample data for test and coding formula.

Regards,

Xiaoxin Sheng