topic Re: Need help converting SQL Statement to DAX in DAX Commands and Tips

Need help converting SQL Statement to DAX

cheid_4838 — Fri, 19 Jan 2024 20:42:49 GMT

I need to convert a SQL statement using "CASE" to a DAX statement. I want to have the results show "1" so I can calculate the total number of orders that have a glnumber beginning with 400 and an invoice number that doesn't begin with S (ignore the bold). I would typically use an IF statement, but I can't do that in Power BI. Any suggestions would be greatly appreciated. Thanks.

, case when left(id.ivd_glnum,3) = '400' and ih.ivh_shipper like 'ALLBRI%' and left(ih.ivh_invoicenumber,1) <> 'S' then 1 else 0 end as Vol

Re: Need help converting SQL Statement to DAX

AlexisOlson — Fri, 19 Jan 2024 21:15:31 GMT

It should be very similar.

Vol = IF ( LEFT ( id[ivd_glnum], 3 ) = "400" && LEFT ( ih[ivh_shipper], 6 ) = "ALLBRI" && LEFT ( ih[ivh_invoicenumber], 1 ) <> "S", 1, 0 )

Check out this article for more SQL to DAX logic:

https://www.sqlbi.com/articles/from-sql-to-dax-string-comparison/

Re: Need help converting SQL Statement to DAX

cheid_4838 — Fri, 19 Jan 2024 21:38:18 GMT

Thanks for the quick response. I tried this, but seem to be having issues with my IF statements. For some reason it's only recognizing new measures and won't allow me to reference the other columns. Would your fix be considered a new measure or column? When I try the new column and measure I get the error message in the screenshot. Have you seen this before?

Re: Need help converting SQL Statement to DAX

AlexisOlson — Fri, 19 Jan 2024 23:23:31 GMT

I wrote it to be used as a calculated column. It won't work for a measure as written (since columns are aggregated in measures).

It looks like those aren't valid column names. What are the actual names of your table and columns when loaded into Power BI?

Re: Need help converting SQL Statement to DAX

cheid_4838 — Sat, 20 Jan 2024 03:33:42 GMT

I was able to get it to work the way I wanted to (1st screenshot) with the below formula, however when I remove the GL Num column and sum the volume I get 2 instead of 1. There are multiple gl numbers per invoice. I only want to sum the volume for gl numbers that are 400 which I thought I was getting with the results in the screenshot. Am I doing something wrong that is not allowing the volume to sum to 1?

VOLUME =

value(If(LEFT(invoicedetail[InvoiceNumber],1)<>"S" && invoicedetail[GL NUM]="400","1","0"))

Re: Need help converting SQL Statement to DAX

Anonymous — Mon, 22 Jan 2024 02:28:27 GMT

Hi cheid_4838 ,

You can try calculated column like below:

Vol = IF ( LEFT('YourTable'[ivd_glnum], 3) = "400" && LEFT('YourTable'[ivh_invoicenumber], 1) <> "S", 1, 0 )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly