Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Conversion of SQL query into DAX expression

Hello,

Hope you all are doing good.

As i am a newbie to this DAX Expresssion i have a hard time converting the below SQL Query into DAX.

Any answer would be much appreciated.

I have 3 steps to do so how do we solve the below problem from your solution. 

SQL Query :

1. SELECT Mat From MaterialProducts

 

2. For the Mat identified in Step1
SELECT MATNBR, shipplant , sum(invcqty), sum(saleslesscreditsamt) from shipingdetails
WHERE BillingmonthKey = previousmonth
AND soldby NOT IN ('20', '30', '40')
GROUP BY matnbr, shipplant
HAVING sum(invcqty) <= 3.000 or
sum(Saleslesscreditsamt ) <= 10

 

Step-3: For the items identified in Step2
SELECT Mat, Number, qty From MaterialProducts
WHERE Mat = MATNBR(From Step2)
and Number = shipplant (From Step2)

 

I tried to convert but it is not working so thought of posting a question for the conversion.

Shipingdetails = EVALUATE
SELECTCOLUMNS (
'shipingdetails',
"MATNBR", "shipplant" , sum(shipingdetails[invcqty]), sum(shipingdetails[saleslesscreditsamt])", 'shipingdetails'[MATNBR], [shipplant] , [sum(shipingdetails[invcqty])], [sum(shipingdetails[saleslesscreditsamt])]
)

 

- Thanks in Advance

 

7 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    Anonymous Try this:

     

    EVALUATE
FILTER (
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( shipingdetails, shipingdetails[MATNBR], MATNBR[shipplant] ),
            shipingdetails[BillingmonthKey] = shipingdetails[previousmonth],
            NOT shipingdetails[soldby] IN { "20", "30", "40" }
        ),
        "@InvoiceQty", CALCULATE ( SUM ( shipingdetails[invc_qty] ) ),
        "@CreditAmt", CALCULATE ( SUM ( shipingdetails[sales_less_credits_amt] ) )
    ),
    [@InvoiceQty] <= 3
        || [@CreditAmt] <= 10
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create a measure as below:

    Flag = 
VAR _tab =
    SUMMARIZE (
        FILTER (
            'shipingdetails',
            shipingdetails[BillingmonthKey] = shipingdetails[previousmonth]
                && NOT ( shipingdetails[soldby] IN { "20", "30", "40" } )
        ),
        'shipingdetails'[matnbr],
        'shipingdetails'[shipplant],
        "@invqty", SUM ( 'shipingdetails'[invc_qty] ),
        "@salesamt", SUM ( 'shipingdetails'[sales_less_credits_amt] )
    )
VAR _invqty =
    SUMX ( _tab, [@invqty] )
VAR _salesamt =
    SUMX ( _tab, [@salesamt] )
RETURN
    IF (
        (
            NOT ( ISBLANK ( _invqty ) )
                && _invqty <= 3
        )
            || (
                NOT ( ISBLANK ( _salesamt ) )
                    && _salesamt <= 10
            ),
        1,
        0
    )

    2. Create a table visual and apply the visual-level filter with the condition(Flag is 1)

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous & AntrikshSharma 

      Appreciate your response in that ,

      Sorry! I have 3 steps to do so how do we solve the below problem from your solution then. 

      1. SELECT Mat From MaterialProducts

       

      2. For the Mat identified in Step1 (For which the solution is provided)
      SELECT MATNBR, shipplant , sum(invcqty), sum(saleslesscreditsamt) from shipingdetails
      WHERE BillingmonthKey = previousmonth
      AND soldby NOT IN ('20', '30', '40')
      GROUP BY matnbr, shipplant
      HAVING sum(invcqty) <= 3.000 or
      sum(Saleslesscreditsamt ) <= 10

       

      Step-3: For the items identified in Step2
      SELECT Mat, Number, qty From MaterialProducts
      WHERE Mat = MATNBR(From Step2)
      and Number = shipplant (From Step2)

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        In order to give you a suitable solution, could you please provide some sample data in the table 'MaterialProducts' and 'shipingdetails' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship created between these two tables? If yes, please also provide the related info(cardinality, cross filter direction etc.). It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

        How to upload PBI in Community

        Best Regards