topic Re: calculate sum with criteria from many columns of another filtered table in DAX Commands and Tips

calculate sum with criteria from many columns of another filtered table

kyrpav — Tue, 28 Jan 2020 02:36:08 GMT

Hello i want to sum a column but i need to filter the table based on data from another table.

So i have table1 where i want to sum points and i want to sum only the record that for the dates and the names and the classes i find in table 2

names	dates	class	points
george	2020-01-01	math	10
george	2020-02-01	physic	13
george	2020-05-01	physic	16
george	2020-06-01	chem	19
george	2020-07-01	math	22
mairi	2020-01-01	math	25
mairi	2020-02-01	physic	28
mairi	2020-05-01	physic	31
mairi	2020-06-01	chem	34
mairi	2020-07-01	math	37
nick	2020-01-01	math	40
nick	2020-02-01	physic	43
nick	2020-05-01	physic	46
nick	2020-06-01	chem	49
nick	2020-07-01	math	52
helen	2020-01-01	math	55
helen	2020-02-01	physic	58
helen	2020-05-01	physic	61
helen	2020-06-01	chem	64
helen	2020-07-01	math	67
sundy	2020-01-01	math	70
sundy	2020-02-01	physic	73
sundy	2020-05-01	physic	76
sundy	2020-06-01	chem	79
sundy	2020-07-01	math	82

And table2

name	date	classes
george	2020-01-01	math
mairi	2020-02-01	physic
nick	2020-05-01	physic
helen	2020-06-01	chem
sundy	2020-07-01	math

I am using measure like this:

Measure 3 = CALCULATE(sum(Table1[points]);Table1[name] in (ALLSELECTED(Table2[name]));Table1[date] in (ALLSELECTED(Table2[date]));Table1[class] in (ALLSELECTED(Table2[class]))) but it does not filter properly,

is there any better way to do this?

Re: calculate sum with criteria from many columns of another filtered table

nhoward — Tue, 28 Jan 2020 03:53:08 GMT

kyrpav

That depends on how and where you want to display that result. A simple way is have a relationship between the 2 tables based on Name. You can then just use a Sum measure, and let the relationships and table do the filtering.

then in a table visual put:

Re: calculate sum with criteria from many columns of another filtered table

kyrpav — Tue, 28 Jan 2020 03:57:46 GMT

My problem is that connection is many to many and even when i try to join all 3 columns i get the same classic problem where it does not find solution and places everywhere the same number.

Re: calculate sum with criteria from many columns of another filtered table

Anonymous — Tue, 28 Jan 2020 08:28:40 GMT

instead of allselelcted try ALLEXCEPT. As it will keep filters on those columns which you mentioned in ALLEXCEPT.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.