Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Prabhu_MDU
Advocate I
Advocate I

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
)

8 REPLIES 8
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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

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

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

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

basically you have to setupi relationship between cte and fact table in powerbi on common column and then everything should work.

 

 I'm assuming cte to fact is one to many relationship.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.