Forum Discussion
Need help converting SQL Statement to DAX
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
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?
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"))
