topic Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column in DAX Commands and Tips

How to write measure in DAX to sum value for two particular warehouse codes in the same column

BhargavVM — Mon, 11 Mar 2024 02:38:19 GMT

Need help in adding the values for two warehouse codes in same column.

Please find the input data

My input data has two columns. Warehouse and Stock.

EX: NSWA is a main warehouse and NSWA-SW is sub warehouse and all other warehouse codes are small branches.

My goal is to sum the stock for all the warehouse codes ending with -SW (sub warehouse NSWA-SW ) and its respective main warehouse (NSWA). My measure should ignore all other warehouse codes.

Please find the output

I could only get the value for the warehouse codes ending with -SW using calculate and filter warehouse code by Right 3 characters.

My Dax Query:

SW Warehouse = Calculate(sum(stock), filter(warehouse, right([warehouse code],3) = "-SW"))

I need the logic how to match the -sw warehouse codes with the respective main branch.

I am using direct query for connection to semantic model cannot use power query.

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

Walter_W2022 — Mon, 11 Mar 2024 05:34:23 GMT

BhargavVM , Please refer to below attached file, I created a virtual table to lookup the stock of parent warehouses, then sum them up to get total, please let me know if you have any questions.

Result:

Virtual table created:

Model:

20240311-file.pbix

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

BhargavVM — Mon, 11 Mar 2024 06:26:26 GMT

Hi Walter_W2022

Thanks for your response.
In my case, I cannot add a new table to my data. I am using direct query mode connecting to the PowerBi semantic model. (My report does not allow me to create either calculated table or calculated column).

It is tricky to work with measure in this case. If you have any alternate solutions using measure, your help will be much appreciated.

Thanks

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

Walter_W2022 — Mon, 11 Mar 2024 06:53:48 GMT

Hi BhargavVM ,

Please see below measure, it should work, the pbix file attached as well. please let me know whether it works or not

_total =

VAR _sw_wh = FILTER(warehouse,RIGHT([warehouse_code],3) = "-SW")

VAR _temp_table =

ADDCOLUMNS(

_sw_wh,

"p_wh", LEFT([warehouse_code],4),

"p_stock", LOOKUPVALUE(warehouse[stock],

warehouse[warehouse_code],LEFT([warehouse_code],4)

)

"Total", [stock]+[p_stock]

)

VAR _result = CALCULATE(SUMX(_temp_table,[Total]))

RETURN

_result

20240311-file-1.pbix

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

BhargavVM — Mon, 11 Mar 2024 07:10:37 GMT

Hi Walter_W2022
In my table I have duplicates. It is giving me error in this line

"p_stock", LOOKUPVALUE(warehouse[stock],

warehouse[warehouse_code],LEFT([warehouse_code],4)

For one warehouse there are multiple rows. How can we aggregate this stock and lookup the value.

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

Walter_W2022 — Mon, 11 Mar 2024 10:39:27 GMT

I am sorry BhargavVM , I could not figure out if there are duplicates and also cannot create virtual table. there are a lot of limitations

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

BhargavVM — Mon, 11 Mar 2024 11:07:41 GMT

Hi No worries Walter. Thanks for providing the solution. I will use as a starting point and try to figure out. Thanks for your help.

Re: How to write measure in DAX to sum value for two particular warehouse codes in the same column

Walter_W2022 — Tue, 12 Mar 2024 01:29:14 GMT

No problem BhargavVM , it is my pleasure.