Need help converting SQL Statement to DAX

[cheid_4838]

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

[Anonymous · Accepted solution]

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

[AlexisOlson]

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/

[cheid_4838]

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?  

[AlexisOlson]

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?

[cheid_4838]

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"))