topic Re: Conversion of SQL query into DAX expression in DAX Commands and Tips

Conversion of SQL query into DAX expression

Anonymous — Wed, 16 Nov 2022 14:47:52 GMT

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

Re: Conversion of SQL query into DAX expression

AntrikshSharma — Mon, 14 Nov 2022 17:11:26 GMT

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 )

Re: Conversion of SQL query into DAX expression

Anonymous — Tue, 15 Nov 2022 07:52:45 GMT

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

Re: Conversion of SQL query into DAX expression

Anonymous — Wed, 16 Nov 2022 14:48:29 GMT

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)

Re: Conversion of SQL query into DAX expression

Anonymous — Wed, 16 Nov 2022 01:12:55 GMT

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

Re: Conversion of SQL query into DAX expression

Anonymous — Wed, 16 Nov 2022 15:05:02 GMT

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)

Re: Conversion of SQL query into DAX expression

Anonymous — Thu, 17 Nov 2022 07:04:24 GMT

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

Re: Conversion of SQL query into DAX expression

Anonymous — Fri, 18 Nov 2022 22:22:47 GMT

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)