Using Two Excel Sheet Tables as filter in sum Dax

[felipevaz]

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?

[wdx223_Daniel]

@felipevaz 

[felipevaz]

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....

[wdx223_Daniel]

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

[felipevaz]

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

[amitchandak]

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

[felipevaz]

https://unimedpoa-my.sharepoint.com/:x:/g/personal/felipe_vaz_unimedpoa_com_br/EV00g4eywn9Eq6J25nmH3... 

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...

[amitchandak · Accepted solution]

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

[felipevaz]

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