Forum Discussion
Helper IUsing Two Excel Sheet Tables as filter in sum Dax
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?
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])))
8 Replies
- amitchandak
Super User
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.
felipevazsave 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
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
Community Champion
- 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
