Forum Discussion

amikm's avatar
amikm
Helper V
4 years ago
Solved

SQL to DAX conversion

I am trying to get the equivalent logic in DAX for below SQL declare @date datetime declare @warehouse nvarchar(225) declare @province nvarchar(225) set @Transdate = '2021-09-18 00:00:00.000'...
DAX function
DAX help needed
dax problem
DAX Query
SQL
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    Happy New Year, pal! Thanks for tagging me for some tricky game of DAX.

     

    I'm afraid you were mislead by PO's sql; first of all, I simplied it this way,

    declare @Transdate datetime
declare @warehouse nvarchar(225)
declare @province nvarchar(225)

set @Transdate = '2016-07-11 00:00:00.000'
set @province = 'BC'
set @warehouse = 'BC-52'
;

with main1 as (
SELECT 
	*
	, ROW_NUMBER() OVER(Partition by itemid   ORDER BY transdate DESC) AS [rn]
	, SUM([Amount]) OVER (PARTITION BY ItemID) AS [Total]
FROM [dbo].[MATDW SAMPLE DATA] dt
WHERE  transdate <=  @Transdate
and province = @province
and warehouseid = @warehouse)

select * from main1 where [rn]=1

    DAX

    Total = 
VAR __cat = MAX( 'MATDW SAMPLE DATA'[Category] )
VAR __summary =
    CALCULATETABLE(
        ADDCOLUMNS(
            ADDCOLUMNS(
                SUMMARIZE(
                    'MATDW SAMPLE DATA',
                    'MATDW SAMPLE DATA'[ITEMID],
                    'MATDW SAMPLE DATA'[WarehouseID]
                ),
                "@amt", CALCULATE( SUM( 'MATDW SAMPLE DATA'[Amount] ) ),
                "@maxdate", CALCULATE( MAX( 'MATDW SAMPLE DATA'[TRANSDATE] ) )
            ),
            "@Cat",
                VAR __dt = [@maxdate]
                RETURN
                    CALCULATE(
                        MAX( 'MATDW SAMPLE DATA'[Category] ),
                        'MATDW SAMPLE DATA'[TRANSDATE] = __dt
                    )
        ),
        ALLSELECTED( 'MATDW SAMPLE DATA'[Category] )
    )
RETURN
    IF(
        __cat IN SELECTCOLUMNS( __summary, "_cat", [@Cat] ),
        SUMX( FILTER( __summary, [@Cat] = __cat ), [@amt] )
    )

     

     

parry2k's avatar
parry2k
Super User
4 years ago

smpa01 wow it is pretty challenging, I did make it thru but have to revisit some point in time. Here are the measures which will take care of it :

 

 

Category for Item = 
MAXX ( 
    SUMMARIZE (
        'MATDW SAMPLE DATA',
        'MATDW SAMPLE DATA'[WarehouseID],
        'MATDW SAMPLE DATA'[ITEMID], 
        'MATDW SAMPLE DATA'[TRANSDATE], 
        "@MaxDt", CALCULATE ( 
                    MAX ( 'MATDW SAMPLE DATA'[TRANSDATE] ), 
                    ALLSELECTED ('MATDW SAMPLE DATA'[TRANSDATE] ) 
                ) 
        ), 
    IF ( 
        'MATDW SAMPLE DATA'[TRANSDATE] = [@MaxDt], 
        CALCULATE ( MAX ('MATDW SAMPLE DATA'[Category] ) ) 
    ) 
) 

Amount by Latest Category (Internal) = 
VAR __table = 
ADDCOLUMNS (
    SUMMARIZE (
        'MATDW SAMPLE DATA',
        'MATDW SAMPLE DATA'[WarehouseID], 
        'MATDW SAMPLE DATA'[ITEMID]
        // ,"@Sum", SUM ('MATDW SAMPLE DATA'[Amount] ) 
    ), 
    "@LatestCategory", CALCULATE ( [Category for Item], REMOVEFILTERS ( 'MATDW SAMPLE DATA'[Category] ) ),
    "@CurrentVisibleCategory", CALCULATE (  MAX ('MATDW SAMPLE DATA'[Category] ) )
)
// VAR __currentVisibleCategory = MAX ( 'MATDW SAMPLE DATA'[Category] )
RETURN 
SUMX ( 
    __table, 
    VAR __latestCategory = [@LatestCategory]
    VAR __currentVisibleCategory = [@CurrentVisibleCategory]
    RETURN
    IF ( __latestCategory = __currentVisibleCategory,
        CALCULATE (
            SUM ( 'MATDW SAMPLE DATA'[Amount] ),
            ALLSELECTED ( 'MATDW SAMPLE DATA'[Category] )
        )
    )
)

Amount by Latest Category = 
IF ( 
    HASONEFILTER ('MATDW SAMPLE DATA'[Category] ), 
    [Amount by Latest Category (Internal)], 
    SUMX ( 
        ADDCOLUMNS ( 
            SUMMARIZE ( 
                'MATDW SAMPLE DATA',
                'MATDW SAMPLE DATA'[WarehouseID],
                'MATDW SAMPLE DATA'[Category] 
            ), 
            "@Amt", [Amount by Latest Category (Internal)] 
        ), 
        [@Amt] 
    )
)

 

 

and here is the output:

 

✨ Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

 



 

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    parry2k  Happy new year !!! and many thanks for taking time out to look into this. Wil review later and let you know.