Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
2 years ago
Solved

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 in...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
AlexisOlson
Super User
2 years ago

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's avatar
cheid_4838
Helper IV
2 years ago

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