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 ...
Need Help
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.. 

v-lili6-msft's avatar
v-lili6-msft
Community 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