Forum Discussion
Power BI Table Relationship Between DimDate & a table contains multiple date columns
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 need to create following measure(s) based on Reporting date slicer which is date column from DimDate
- Total Incident Created
- Total Incident Cancelled
- Total Incident Resolved
- Total Incident Closed
- Total Incident Re Opened
- Total Incident Open
We need to understand how to make relationship so that these measures can be created & it will give correct values.
Thanks,
PBI V2
Anonymous
you can link the dimdate to al the columns.
( you will have 1 active relationship, and all the remaining will be inactive ) ( set the active relationship to the date that you will mostly be using in the report ).
now create a measure for each calculation, and use userelationship, to activate the relationship that you want your measure to be based on
calculation pattern : calculate ( calculation , userelationship( dimdate[date], table[datecolumn) )datecolumn will depend on the measure you are calculating.
hope this helps.
2 Replies
- Daniel29195
Community Champion
Anonymous
you can link the dimdate to al the columns.
( you will have 1 active relationship, and all the remaining will be inactive ) ( set the active relationship to the date that you will mostly be using in the report ).
now create a measure for each calculation, and use userelationship, to activate the relationship that you want your measure to be based on
calculation pattern : calculate ( calculation , userelationship( dimdate[date], table[datecolumn) )datecolumn will depend on the measure you are calculating.
hope this helps.