Forum Discussion
Need help converting SQL Statement to DAX
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
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
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/
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?
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?
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
