Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have two tables Product and Sales. The relationship between two tables all like below ( In Sql)
Select *from Product P
Inner Join Sales S
On S.SalesProductID = P.ProductID
and P.TransactionDate Between S.SalesValidFromDate and S.ValidToDate
While Modelling tables in Power BI/SSAS Tabular How do we specify the above relationship between these tables. I Couldn’t find any option to write conditions/expressions in the Relationship options provided. Any suggestions much appreciated. ( I have come across calculated tables is that only way to achieve this scenario?)
Solved! Go to Solution.
Hi @baijumohan1990 ,
We can create a calculated table as below in power bi.
Table =
VAR a =
FILTER ( 'Product', 'Product'[Product ID] IN VALUES ( Sales[Product ID] ) )
VAR c =
ADDCOLUMNS (
a,
"fd", CALCULATE (
MAX ( Sales[SalesValidFromDate ] ),
FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
),
"tod", CALCULATE (
MAX ( 'Sales'[ValidToDate] ),
FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
)
)
RETURN
FILTER (
c,
'Product'[TransactionDate] >= [fd]
&& 'Product'[TransactionDate] <= [tod]
)
For more details, please check the pbix as attached.
Hi @baijumohan1990 ,
We can create a calculated table as below in power bi.
Table =
VAR a =
FILTER ( 'Product', 'Product'[Product ID] IN VALUES ( Sales[Product ID] ) )
VAR c =
ADDCOLUMNS (
a,
"fd", CALCULATE (
MAX ( Sales[SalesValidFromDate ] ),
FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
),
"tod", CALCULATE (
MAX ( 'Sales'[ValidToDate] ),
FILTER ( Sales, Sales[Product ID] = 'Product'[Product ID] )
)
)
RETURN
FILTER (
c,
'Product'[TransactionDate] >= [fd]
&& 'Product'[TransactionDate] <= [tod]
)
For more details, please check the pbix as attached.
Hi @v-frfei-msft
I have a similar case, But there is no link key between the two tables.
ONLY this condition P.TransactionDate Between S.SalesValidFromDate and S.ValidToDate
So, what are the possible ideas for this?
Thanks in Advance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |