Forum Discussion
Translate self-join query
Hi there,
Is it possible to transalate following self-join SQL query in to a DAX statement or any better way to achieve the same?
select
sum(fct.Amount) as Amount
from Fact as fct
inner join
DimReceipt as r
on fct.ReceiptKey = r.ReceiptKey
inner join
DimReceipt as Version
on r.ReceiptNo = Version.ReceiptNo
inner join
DimComponent as DC
on fct.ComponentKey = DC.ComponentKey
inner join
DimCategory as cat
on fct.CatKey = cat.CatKey
inner join DimTran t on t.TranKey = fct.TranKey
where '2021-01-31' between Version.ValidFrom and Version.ValidTo
and '2021-01-31' > fct.ValidFrom
and t.DebtTransaction = 'SpecialTrans'
group by Version.Stage,
cat.CatCode
, case when Version.FOrPU = 'FU' then DC.ComponentName
else
case when Version.PA = 1 then 'PA'
else 'UP'
end
end
Thank you
4 Replies
- amitchandakSuper User
Anonymous , Add one more DimReceipt and call it to say, DimVersion and use that.
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- AnonymousNot applicable
Hi amitchandak
Appreciate your time. The sample data is as follows.
For 1st record in fact table, the related (target) record in Dim table is red one.
Similarly, for 2nd in fact table, the related (target) record in Dim table is green one.
Expected result: Sum amount in fact table, based on active record (red or green) in Dim table.
Let me know if you have more questions.
- v-lionel-msftCommunity Support
Hi Anonymous ,
Please provide the expected output table.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.