Forum Discussion

Prabhu_MDU's avatar
Prabhu_MDU
Advocate I
7 years ago

Alternate for my SQL query in DAX

,cte_Allocation as
(
select distinct
cr.RemittanceCode,
sum(fa.AllocAmount_OC_A) as 'AllocationAmount_Orig',
sum(fa.AllocAmount_FC_A) as 'AllocationAmount_Func'
from
cte_Remittance cr /* <= A data-table is available in the same name in power bi */
left join Fact_Allocation fa
on cr.LedgerBalance_ID = fa.LedgerBalance_1_ID
where fa.[Date] <= @AsOfDate
group by
cr.RemittanceCode
)

Need Help

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Prabhu_MDU can you share how you joined the table  in powerBI? Do you have calendar table in your model? I guess you @AsofDate is a parameted and you want to run query on or before that? Assuming you will use this as a slicer in PowerBI.

    • nirvana_moksh's avatar
      nirvana_moksh
      Impactful Individual
      7 years ago

      parry2k - just wanted to confirm if it still stays true that we cannot run queries with a parameter defined in SQL like a stored procedure like exec stored_proce @Date and then allowing users with a date slicer which feeds in that defined parameter, but this can be achieved in Paginated Reports?

    • Prabhu_MDU's avatar
      Prabhu_MDU
      Advocate I
      7 years ago

      Hi parry2k, 

       

      I have not joined the tables till now in power bi.. 

       

      all are individual tables as of now, please find the tables details below which are currently available in power bi

       

      1) Allocation 

      2) Cte_Remittance

       

      these two tables needs to be joined (left join) and bring the query as given above .. 

       

      there is a column called "SelectedValues" in Cte_Remittance which will hold the @AsOfDate value 

       

      Thanks, 

      Prabhu

      • Prabhu_MDU's avatar
        Prabhu_MDU
        Advocate I
        7 years ago

        Hi Guys, 

         

        for time being, let's forget about the Parameter AsOfDate, all I am looking for is the alternate query select query with left join using group by in it.. 

         

        Kindly help me.. 

         

        refined query

        --------------

         

        ,cte_Allocation as
        (
        select distinct
        cr.RemittanceCode,
        sum(fa.AllocAmount_OC_A) as 'AllocationAmount_Orig',
        sum(fa.AllocAmount_FC_A) as 'AllocationAmount_Func'
        from
        cte_Remittance cr /* <= A data-table is available in the same name in power bi */
        left join Fact_Allocation fa
        on cr.LedgerBalance_ID = fa.LedgerBalance_1_ID
        group by
        cr.RemittanceCode
        )

         

        Thanks a lot..