cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amikm
Helper V
Helper V

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'
set @province = 'BC'
set @warehouse = 'BC-01'

 

----- THIS CTE RANKS THE ITEMS TO GET THE MOST RECENT category -----------
with main1 as (
SELECT * ,
ROW_NUMBER() OVER(Partition by itemid, warehouseid ORDER BY transdate DESC) AS rn
FROM [EDW].[FACT].[InventoryBalancedet_2] dt
WHERE province = @province
and warehouseid = @warehouse
and transdate <= @Transdate
),

------ THIS CTE RANKS amount of an item by warehouse -----------


main2 as (
select sum(Amount) as AMOUNT
, ItemID
,warehouseid

FROM [EDW].[FACT].[InventoryBalancedet_2]
WHERE province = @province
and warehouseid = @warehouse
and transdate <= @Transdate
group by ItemID , warehouseid
),


------------ COMBINING THE QUERIES TOGETHER --------------------
main3 as (
SELECT m2.AMOUNT, m2.warehouseid , m1.Category
FROM
main1 m1
inner join main2 m2 on m1.warehouseid = m2.warehouseid
and m1.ItemID = m2.ItemID
and m1.rn = 1
)

select sum(m3.total) ,m3.Category
FROM main3 m3
group by m3.Category

1 ACCEPTED SOLUTION

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

CNENFRNL_0-1640997427482.png

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] )
    )

CNENFRNL_1-1640997539713.png

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@CNENFRNL well done, but one thing is missing if you turn on the total that doesn't look correct and tht's why in my measure I have to create a final measure to take care of the total. FYI

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@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:

parry2k_0-1640973827462.png

 

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.

 



 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


smpa01
Super User
Super User

@amikm can you provide a SQL DDL? or at least provide a sizeable sample of [InventoryBalancedet_2] in a pbix, upload that in a g drive/1 drive and share the link here? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Thanks  @smpa01 , Please find the sample data and file 

i have attached a zip file containing the zip file & the sql query .

i will like to get the results (Amount by category) as shown in the query .

Also i will like to use the filters below on the result set
1) transdate
2) warehouse
3) province


https://drive.google.com/drive/folders/1z0OsSNTsOk2_j-AtD0IV9eDa0_P-zMo4?usp=sharing

@amikm  I deleted my earlier answer cause I realized it was wrong.

I spent some time with this but could not make it to work, looping @parry2k @AlexisOlson @CNENFRNL 

@parry2k @AlexisOlson @CNENFRNL  OP has a TSQL query like following

 

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'



----- THIS CTE  RANKS THE ITEMS TO GET THE MOST RECENT category -----------
;with main1 as (
SELECT  * ,
ROW_NUMBER() OVER(Partition by itemid, warehouseid   ORDER BY transdate DESC) AS rn    
FROM [newtest].[dbo].[MATDW SAMPLE DATA] dt
WHERE  transdate <=  @Transdate
and province = @province
and warehouseid = @warehouse

)
,main2 as (
select sum(Amount) as AMOUNT
, ItemID
,warehouseid

FROM [newtest].[dbo].[MATDW SAMPLE DATA]
WHERE  transdate <=  @Transdate
and province = @province
and warehouseid = @warehouse

group by ItemID , warehouseid  
)

,main3 as (
SELECT m2.AMOUNT, m2.warehouseid , m1.Category
FROM
main1 m1
inner join main2 m2 on m1.warehouseid = m2.warehouseid
and m1.ItemID = m2.ItemID
and m1.rn = 1
)
,main4 as (select sum(m3.AMOUNT)  AS AMOUNT ,m3.Category
FROM main3 m3
group by  m3.Category )

select * from main3 /*where rn=1*/

--select *
--FROM [newtest].[dbo].[MATDW SAMPLE DATA]
--WHERE  transdate <=  @Transdate
--and province = @province
--and warehouseid = @warehouse and ITEMID=10000

 

which produces this

smpa01_0-1640961065740.png

The equivalent DAX query for a dervied table would be following

 

Table = 
VAR _basicFilterDate =
    DATE ( 2016, 7, 11 )
VAR _basicFilterProvince = "BC"
VAR _basicFilterWarehouse = "BC-52"
VAR _filteredTable =
    FILTER (
        'MATDW SAMPLE DATA',
        'MATDW SAMPLE DATA'[Province] = _basicFilterProvince
            && 'MATDW SAMPLE DATA'[TRANSDATE] <= _basicFilterDate
            && 'MATDW SAMPLE DATA'[WarehouseID] = _basicFilterWarehouse
    )
var _sum = ADDCOLUMNS(_filteredTable,"sum",SUMX(FILTER(_filteredTable,[ITEMID]=EARLIER([ITEMID])&&[WarehouseID]=EARLIER([WarehouseID])),[Amount]))
var _date = ADDCOLUMNS(_sum,"maxDT",MAXX(FILTER(_sum,[ITEMID]=EARLIER([ITEMID])&&[WarehouseID]=EARLIER([WarehouseID])),[TRANSDATE]))
var _filter = FILTER(_date,[TRANSDATE]=[maxDT])
var _groupBY = SUMMARIZE(_filter,[Category],[sum])
var _aggregation = GROUPBY(_groupBY,[Category],"_sum",SUMX(CURRENTGROUP(),[sum]))
RETURN _aggregation

 

 

which returns this

smpa01_1-1640961447003.png

 

However, how can this be converted to measure that reponses to a slicer

 

VAR _basicFilterDate =
    DATE ( 2016, 7, 11 )
VAR _basicFilterProvince = "BC"
VAR _basicFilterWarehouse = "BC-52"

 

Created a pbix for your convenience as it has bothered me for a while. 

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

CNENFRNL_0-1640997427482.png

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] )
    )

CNENFRNL_1-1640997539713.png

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL happy new year !!! Thanks for taking time out to look into this. Will review and rep on or after 2nd Jan, 22.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


ValtteriN
Super User
Super User

Hi,

So if I understood corectly you want to get the latest warehouse values in a calculated table which is in essence a summarized version of your InventoryBalancedet_2 table? 

Test data:

ValtteriN_0-1640681943687.png

 

Dax (Table):

LatestHistory =
SUMMARIZE(filter(WarehouseHistory,WarehouseHistory[Date]=calculate(MAX(WarehouseHistory[Date]),ALLEXCEPT(WarehouseHistory,WarehouseHistory[Item],WarehouseHistory[Warehouse]))),

WarehouseHistory[Item],WarehouseHistory[Warehouse],WarehouseHistory[Date],"Amount",SUM(WarehouseHistory[QTY]))

End result:
ValtteriN_1-1640682017517.png

 


I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




rbriga
Super User
Super User

Is it possible for you to save this as a stored procedure on the SQL Server (or view, but you'll need to do away with the declared variables),

Then call it in Power Query? 

 

Not only would it save you the trouble of re-creating your hard work, it would also move that transformation to to source, which is preferable.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!