Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

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

    hello Anonymous , 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.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

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

    hello Anonymous , 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello Anonymous 

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous 

        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

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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] )
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        aduguid
        Icon for Memorable Member rankMemorable Member
        2 years ago

        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] )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • sudhisami_azure's avatar
    sudhisami_azure
    Frequent Visitor
    2 years ago
    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
    )