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 ,

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)