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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Visual Filtering

Hi All,

 

I currently have two tables as per the below dataset examples;

 

Task IDTonnage
11
22
34
42
52
64

1st table

 

Task IDHeight
12
21
32

2nd table

 

Please note the 1st table with 'Tonnage' included has more 'Task ID' fields than the second table. I want to display a card visual showing the total SUM tonnage but only show the SUM tonnage for all Task ID's in the 2nd table. Please note both real sets of data are over 100,000 rows long so it is not practical and manually filter the 1st tables 'Task ID'.

 

Many thanks,

 

E

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try like

calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in Values(Table2[Task ID])))
calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in allselected(Table2[Task ID])))

 

Or use treatas

https://docs.microsoft.com/en-us/dax/treatas-function

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
sdjensen
Solution Sage
Solution Sage

Create a new table with the distinct values of task_id from 2nd table and link the 1 tables to the new table. Then create a filter in the report where you deselect values on blank task_id (first select all and then deselect blank - this will create a different from filter that will include new task_id when models update). This should only include task_id present in the 2nd table.

/sdjensen
amitchandak
Super User
Super User

@Anonymous , Try like

calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in Values(Table2[Task ID])))
calculate(sum(table1[Tonnage]),filter(Table1, Table1[Task ID] in allselected(Table2[Task ID])))

 

Or use treatas

https://docs.microsoft.com/en-us/dax/treatas-function

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Pragati11
Super User
Super User

Hi @Anonymous ,

 

Have you got any relationship between these 2 tables?

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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