Power BI Measure - Creating a DAX measure which uses multiple relationship between 2 tables

[PBI_V2]

Hello All,

We have a DimDate table which is our Date Dimension table. We have a Incident_Detail data table which contains following date column(s):

1) Created_Date
2) Cancelled_Date
3) Resolved_Date
4) Closed_Date
5) Re Opened_Date
6) Open_Date

Now we created relationship between DimDate[Date] to All the dates columns available in the Incident_Detail Table. We have active relationship with Created_Date. We need to create a measure that includes both Created_Date & Resolved_Date

Below is the Pseudo code for the measure:

Total Open Incident=

IF(Created_DateKey<=Reporting_Date,
(IF(OR(state="On Hold",state="In Progress",Resolved_Datekey>=Reporting_Date),
count(Incident),0)),(0))

Note- Reporting_Date is the slicer value from DimDate[Date]

Could you please help us in writing the DAX measure for above Pseudo code

Thanks,

PBI V2

[sudhisami_azure]

Total Open Incident =
VAR Reporting_Date = MAX(DimDate[Date]) // Assuming you want to use the maximum date selected in the slicer
RETURN
CALCULATE(
COUNTROWS(Incident_Detail),
Incident_Detail[Created_Date] <= Reporting_Date,
OR(
Incident_Detail[state] = "On Hold",
Incident_Detail[state] = "In Progress",
Incident_Detail[Resolved_Date] >= Reporting_Date
),
USERELATIONSHIP(DimDate[Date], Incident_Detail[Created_Date]) // Establishing the relationship to Created_Date
)

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @aduguid  provided, and i want to offer some more infotmation for user to refer to.

hello @PBI_V2 , you can create a measure.

Total Open Incident =
VAR a =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Incident_Detail ),
        Incident_Detail[Created_Date] <= a,
        OR (
            Incident_Detail[state] IN { "On Hold", "In Progress" },
            Incident_Detail[Resolved_Date] >= a
        ),
        CROSSFILTER ( DimDate[Date], Incident_Detail[Created_Date], NONE )
    )

Best Regards!

Yolo Zhu

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

[PBI_V2]

Hello @v-xinruzhu-msft 

Thanks for your response. We are getting the below error message with the formula provided by you.

CROSSFILTER function can only use the two column references participating in a relationship.

Thanks,

PBI V2

[v-xinruzhu-msft]

Hi @PBI_V2 

Please make sure that the relationship between created_date and calendar date is active, and the created_date and calendar date shold be put in the crossfilter() function.if it still cannot work, can you show the code you have created?

Best Regards!

Yolo Zhu

[PBI_V2]

Hello @v-xinruzhu-msft 

We have active relationship beween DimDate[Date] & Incident_Detail[Created_Date]. Below is the screenhsot:

Below is the actual code

Total Open Incident =

CALCULATE (

    COUNTROWS ( Incident_Detail ),

    FILTER (

        Incident_Detail,

        Incident_Detail[Created_Date] <= MAX ( DimDate[Date] ) &&

        (

            Incident_Detail[state] = "On Hold" ||

            Incident_Detail[state] = "In Progress" ||

            Incident_Detail[Resolved_Date] >= MAX ( DimDate[Date] )

        )

    ),

    USERELATIONSHIP ( Incident_Detail[Resolved_Date], DimDate[Date] )

)

Thanks,

PBI V2

[v-xinruzhu-msft]

Hi @PBI_V2 ,

Thanks for your quick reply, the code you created is different from the code i have offered, you can copy the code i offered, and try it.

Best Regards!

Yolo Zhu

[PBI_V2]

Hello @v-xinruzhu-msft 

Your Code is working. We are validating the data.. We will update you soon.

Thanks,

PBI V2

[PBI_V2]

Hello @v-xinruzhu-msft @aduguid ,

Thanks a Lot!... Finally It is working.....

Thanks,

PBI V2

[PBI_V2]

Hello @aduguid ,

We have tried the latest formula provided by you. It is giving the below error when we add the measure to Visual:

Thanks,

PBI V2

[aduguid]

Give this a try 

Total Open Incident =
CALCULATE (
    COUNTROWS ( Incident_Detail ),
    FILTER (
        Incident_Detail,
        Incident_Detail[Created_Date] <= MAX ( DimDate[Date] ) &&
        (
            Incident_Detail[state] = "On Hold" ||
            Incident_Detail[state] = "In Progress" ||
            Incident_Detail[Resolved_Date] >= MAX ( DimDate[Date] )
        )
    )
)

[PBI_V2]

Hello @aduguid ,

Thanks for your response. we have validated the data after applying the formula provided by you. It looks like Resolved_Date condition is not working beause it is an inactive relationship. 

Total Open Incident =

CALCULATE (

    COUNTROWS ( Incident_Detail ),

    FILTER (

        Incident_Detail,

        Incident_Detail[Created_Date] <= MAX ( DimDate[Date] ) &&

        (

            Incident_Detail[state] = "On Hold" ||

            Incident_Detail[state] = "In Progress" ||

            Incident_Detail[Resolved_Date] >= MAX ( DimDate[Date] )

        )

    )

)

We tried USERELATIONSHIP

USERELATIONSHIP( DimDate[DateKey], Daily_Incident_Detail[Resolved_Datekey]),(Daily_Incident_Detail[Resolved_Datekey] >= MAX ( DimDate[DateKey] )

But it is giving below error:

Operator or expression '( )' is not supported in this context.

Thanks,

PBI V2

[aduguid]

My apologies, try this one 

Total Open Incident =
CALCULATE (
    COUNTROWS ( Incident_Detail ),
    FILTER (
        Incident_Detail,
        Incident_Detail[Created_Date] <= MAX ( DimDate[Date] ) &&
        (
            Incident_Detail[state] = "On Hold" ||
            Incident_Detail[state] = "In Progress" ||
            Incident_Detail[Resolved_Date] >= MAX ( DimDate[Date] )
        )
    ),
    USERELATIONSHIP ( Incident_Detail[Resolved_Date], DimDate[Date] )
)