Forum Discussion

Prabhu_MDU's avatar
Prabhu_MDU
Icon for Advocate I rankAdvocate 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 ...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper 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.

Prabhu_MDU's avatar
Prabhu_MDU
Icon for Advocate I rankAdvocate 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
    Icon for Advocate I rankAdvocate 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.. 

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      Prabhu_MDU can you send pbix file if it doesn't contain sensitive information and will do it for you, I guess cte table has one to many relationship with fact table?

      • Prabhu_MDU's avatar
        Prabhu_MDU
        Icon for Advocate I rankAdvocate I
        7 years ago

        sorry, I could not share the pbix file, since it has very sensitive data.. 

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      hi, Prabhu_MDU

      What is the cardinality of the relationship between cte_Remittance with Fact_Allocation?

      Usually, you need to create a relationship  between cte_Remittance with Fact_Allocation, then use SUM Function to add two measure 'AllocationAmount_Orig' and  'AllocationAmount_Func' and then  drag RemittanceCode  'AllocationAmount_Orig' and  'AllocationAmount_Func' into table visual, drag SelectedValues into a slicer. then it will work.

      If not your case, please share some simulated data and expected output for us.

       

       

      Best Regards,

      Lin