Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors