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...
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    MaterialProducts Table

    SQL Query : SELECT * From MaterialProducts

    DateCreated                          Mat                            MatID       Qty           Number
    2022-11-16 10:45:23.657 000000000010163475  10163475      1                031
    2022-11-16 10:45:23.657 000000000010041325  10041325      3                071
    2022-11-16 10:45:23.657 000000000010198072  10198072      5                081
    2022-11-16 10:45:23.657 000000000010178854  10178854      7                011

    Step 1 : Expected SQL Query :- SELECT Mat From MaterialProducts


    Shipingdetails Table

    SQL Query : SELECT * From Shipingdetails

    MATNBR                       shipplant    invcqty   saleslesscreditsamt BillingmonthKey soldby
    000000000010189497     018          1.000             8.07                       20221101        10
    000000000010231301     023          1.000           13.96                       20221101        10
    000000000010158010     029          1.000            4.34                        20221101        10
    000000000010051031     018          1.000            6.23                        20221101        10

    Step 2 :
    Expected SQL Query :-
    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

    Assume that the Result of this query is stored in a temp table named "TempShipingdetails"

    Note - previousmonth should be the month calculated by todays date and it should be in format YYYYMMDD , For Eg :20221001
    For the previous month i am using the DAX logic

    var previousmonth = DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())

     

    Step 3:
    Expected SQL Query :
    SELECT Mat, Number, Qty From MaterialProducts M
    WHERE M.Mat = MATNBR(From TempShipingdetails)
    and M.Number = shipplant (From TempShipingdetails)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

      You can create a measure as below to get it base on the formula which provide by AntrikshSharma , and create a table visual with filter conditon Flag is 1 (see the screenshot below). Please find the details in the attachment.

      Flag = 
VAR _selmat =
    SELECTEDVALUE ( 'MaterialProducts'[Mat] )
VAR _selnum =
    SELECTEDVALUE ( 'MaterialProducts'[Number] )
VAR _prmonth =
    EOMONTH ( TODAY (), -1 )
VAR _previousmonth =
    VALUE ( YEAR ( _prmonth ) & FORMAT ( _prmonth, "mm" ) & "01" ) //just for testing
VAR _tab =
    FILTER (
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE (
                    FILTER (
                        shipingdetails,
                        'Shipingdetails'[MATNBR] = _selmat
                            && 'Shipingdetails'[shipplant] = _selnum
                    ),
                    shipingdetails[MATNBR],
                    'Shipingdetails'[shipplant]
                ),
                shipingdetails[BillingmonthKey] = _previousmonth,
                NOT shipingdetails[soldby] IN { "20", "30", "40" }
            ),
            "@InvoiceQty", CALCULATE ( SUM ( Shipingdetails[invcqty] ) ),
            "@CreditAmt", CALCULATE ( SUM ( Shipingdetails[saleslesscreditsamt] ) )
        ),
        [@InvoiceQty] <= 3
            || [@CreditAmt] <= 10
    )
RETURN
    IF (
        _selmat = MAXX ( _tab, [MATNBR] )
            && _selnum = MAXX ( _tab, [shipplant] ),
        1,
        0
    )

      Best Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I am still facing one issue (Issue is i am not able to retreive the correct value of qty or the sum value of qty from MaterialProducts in step 3 because it is not filtering based on the flag if i create a visual(To be precise it is not filtering based on the Step 3) ) , Say for instance i want to remove this flag concept and for Step 3, I just need to return the Qty or the Sum of the Qty based on Step3 (i.e the filter (WHERE Mat = MATNBR(From Step2)
        and Number = shipplant (From Step2)) instead of flag 1 or 0 in if condition. Also just had a small question why this MAXX function is applied , it will just return the largest MAT_NBR or largest Ship_Plant right. Sorry! I am going back and forth with this question, i am a newbie so was trying to understand and make this work. Thank You for understanding and really appreciate that.

         

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