Re: SQL to Dax (convert)

RMD · ‎04-20-2020

Hello Experts,

I have a sql command but I need to have dax formula. The problem i don't know how to convert this formula in a Dax formula.

sum(case when tr1.SDATEOCCURRED <= tr.sdateoccurred

then isnull(dt.samount, 0)

+ isnull(dt_vat.samount, 0)

end) on_time_amount

I have the following tables in Powerbi: TRANS, TRANS (1) and Detail

In the sql report I have the folloing tables: tr1 = Trans (1), Tr = Trans and dt=Detail.

many thanks in advance for your help!!!

v-lili6-msft · ‎04-20-2020

hi @RMD

What is the relationship among these thress tables? and do you want to create a measure or a calculate table?

and you may use SUMX Function in the formula

https://radacad.com/sum-vs-sumx-what-is-the-difference-of-the-two-dax-functions-in-power-bi

and sample data and expected output would help tremendously.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.

parry2k · ‎04-20-2020

@RMD please paste the sample data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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.