SQL to DAX conversion

[amikm]

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

[parry2k]

@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

[parry2k]

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

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

[smpa01]

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

[amikm]

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

[smpa01]

@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

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

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. 

[CNENFRNL · 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

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

[smpa01]

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

[ValtteriN]

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:

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:

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

[rbriga]

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.