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_D...
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.

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello Anonymous 

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        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