topic Re: Using Two Excel Sheet Tables as filter in sum Dax in DAX Commands and Tips

Using Two Excel Sheet Tables as filter in sum Dax

felipevaz — Tue, 01 Dec 2020 02:32:14 GMT

Hi,

I have three Excel tables: (start table, Codes and Subcodes)

Using Dax, I want to sum the field Value as a measure when I change the code and subcode (update all)...

All table are in the model.

I trying to use

calculate(sum(table[value],filter(table,table[codes] = codes[codes] && filter(table,table[subcode] = subcodes[subcodes])

off course this is giving me an error. I dont know how to implement that. Could you help me please?

amitchandak — Tue, 01 Dec 2020 02:38:51 GMT

felipevaz , Ideally. You can join code and sub code with start table and they should filter automatically.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

wdx223_Daniel — Tue, 01 Dec 2020 03:33:20 GMT

felipevaz — Tue, 01 Dec 2020 03:42:32 GMT

save to your computer the table was loaded into the model

amitchandak I cant joining the tables cos that is costing a lot of time when refreshing....

thats why I tried to load data into data model and use dax to filter the data

I forgot to mention that I need to sum the total of value, sum the total of value of selected Code and subcode...

felipevaz — Tue, 01 Dec 2020 03:44:12 GMT

Heey, thnx wdx223_Daniel i didnt know that I could do it...Nice!!

In my model I need to allow user to set Code and/or subcode independently...

if the subcode is blank then just use the code....

amitchandak — Tue, 01 Dec 2020 04:35:00 GMT

felipevaz , Try meausre like

calculate(sum(table[value]),filter(table,table[codes] in values(codes[codes]) && table[subcode] in values(subcodes[subcodes])))

calculate(sum(table[value]),filter(table,table[codes] in allselected(codes[codes]) && table[subcode] in allselected(subcodes[subcodes])))

wdx223_Daniel — Tue, 01 Dec 2020 05:36:52 GMT

felipevaz have you try my code? code and subcode are independent. you can update those columns, and click refresh all

felipevaz — Sat, 05 Dec 2020 00:57:30 GMT

amitchandak worked like a charm! Really thank you for supporting me.

felipevaz — Sat, 05 Dec 2020 00:56:06 GMT

wdx223_Daniel works as well! thank you. I learned a lot with your code. hugs!