cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
armand453
Frequent Visitor

Apply Filter with multiple values

Hello, I am trying to apply a filter on a table with values of another table column, I have tried this formula with no sucess :

 

Formula = sumx(CALCULATETABLE(Table1; Table1[Category] = DISTINCT(Table2[Cat])); Table1[Value])

 

My tables are :

 

Table 1

 

CategoryValue

15
210
315
420
5

25

 

Table 2 

 

Cat

1
2
3
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@armand453 Please try below measure.

 First I created a table for unique categories. go to modeling tab and click on new table

 

Categories = VALUES('Table'[Category])

 

then create a measure

 

Measure = 
VAR _selectedCategories = VALUES(Categories[Category])
RETURN CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] IN _selectedCategories))

 

Using category value from categories table in slicer and based on the selected values returning sum.

If it solved your purpose mark it as solved.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@armand453 Please try below measure.

 First I created a table for unique categories. go to modeling tab and click on new table

 

Categories = VALUES('Table'[Category])

 

then create a measure

 

Measure = 
VAR _selectedCategories = VALUES(Categories[Category])
RETURN CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category] IN _selectedCategories))

 

Using category value from categories table in slicer and based on the selected values returning sum.

If it solved your purpose mark it as solved.

Anonymous
Not applicable

Column = LOOKUPVALUE(Table1[Value],Table2[Cat],Table1[Cat])
 
Create new measure on it to sum the values.
Capture1.PNG

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors