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 ,

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

    • 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