conditional relationship between tables

[Anonymous]

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!

[Alex87 · Accepted solution]

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!

[danextian]

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] )
    )
)

[Anonymous]

I added this column to the OFF table. Now should I link this OFF table to the AGGR table using the cod_prom and codprom columns? Because all the other information I need is in the AGGR table.