Conversion of SQL query into DAX expression

[TSN]

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

[Anonymous]

Hi @TSN ,

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

[TSN]

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]

Hi @TSN ,

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

[TSN]

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]

Hi @TSN ,

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

[TSN]

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)

[AntrikshSharma]

@TSN 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
)