Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • 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.

    • Anonymous's avatar
      Anonymous
      Not 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-msft's avatar
        v-lionel-msft
        Community Support

        Hi Anonymous ,

         

        Please provide the expected output table.

         

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.