Forum Discussion
conditional relationship between tables
Hi, I need to create a complex relationship between two tables, AGGR and OFF based on dates. Basically the relationship between AGGR[COD_PROM] and OFF[codprom] should only occur if AGGR[date]>= OFF[startdate] and AGGR[date]<=OFF[enddate].
i want that relationship between on columns, as keys, cod_prom and codprom works ONLY if a date in AGGR is on range of startdate and end date of table OFF. Thanks!
Hello,
In terms of modelling, I am using a simple star schema with 1 to many relationship between a dimension table that contains Cod_Prom - you can create this table by duplicating one of the tables and keeping only this column without duplicates
in terms of measure you can create the following:
Solution = IF( MAX(AGGR[date]) >= MAX(OFF[startdate]) && MAX(AGGR[date]) <= MAX(OFF[enddate]), CALCULATE( SELECTEDVALUE(AGGR[date]), TREATAS(VALUES(AGGR[cod_prom]), OFF[Codprom])))the result is the following (cod prom coming from the dimension and the measure created above)
I tested the scenario when the condion is not met. The date will not appear as shown below
If it answers your question, please mark my reply as the solution. Thanks!
3 Replies
Hi Anonymous
While you can drag a column from a table onto a column from another table and that will most likely create a relationship, in your case using relationships won't give the correct result. There is not such thing as conditional relationship in the context of Power BI. What you can do is create a calculated column to retrieve the matching value from another table based on certain conditions and use that in a relationship or create calcualted columns/measures for all the values you want to retrieve from a disconnected table. Try this as a calc column in OFF
Calc Date = CALCULATE ( MAX ( AGGR[date] ), FILTER ( ALL ( AGGR ), AGGR[date] <= EARLIER ( off[startdate] ) && AGGR[date] >= EARLIER ( off[startdate] ) ) )Hello,
In terms of modelling, I am using a simple star schema with 1 to many relationship between a dimension table that contains Cod_Prom - you can create this table by duplicating one of the tables and keeping only this column without duplicates
in terms of measure you can create the following:
Solution = IF( MAX(AGGR[date]) >= MAX(OFF[startdate]) && MAX(AGGR[date]) <= MAX(OFF[enddate]), CALCULATE( SELECTEDVALUE(AGGR[date]), TREATAS(VALUES(AGGR[cod_prom]), OFF[Codprom])))the result is the following (cod prom coming from the dimension and the measure created above)
I tested the scenario when the condion is not met. The date will not appear as shown below
If it answers your question, please mark my reply as the solution. Thanks!
