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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
SWeijman
New Member

Creating a dynamic table based on on-screen filters

Tables.jpg

I would like to measure to what extend items are available for a certain project within certain types of storage locations. I’ve got two fact tables (‘Required items per project’ and ‘Available items’) and two filter tables (‘Item’ and ‘Location’). For example, P1 requires 25x item 001 and there are 25 items available (100%) organization-wide. However, as our users would like to be able to filter on several specific combinations of locations to deliver items, dynamic filters are required. In case of only counting the available material with type of storage set at User (locations A,B, D, F) for project P1, the graphs should show 17/25 = 68% availability.

 

So far, we’ve only succeeded in doing this with predetermined filters and merged tables in the powerquery. But as our list of locations is far longer than 6 and doing this with merged tables would result in an enormous amount of columns, we would like to see if it’s possible to use a filter on the board’s main screen for this operation. Could you please help us with the required steps to achieve this?

 

We're now looking at the following steps:

- Create dynamic table, based on on-screen location filter;

- Create SUM measures (or column) for availabilty per item, based on aforementioned dynamic table;

- Calculate availability (Available items / required items). 


Kind regards,
Stephan

1 REPLY 1
Anonymous
Not applicable

Here's the full solution.

 

First, the model:

The modelThe model

Then, the report:

The reportThe report

 

And here are the measures:

 

# Available Items = SUM( 'Item Availability'[Item Count] )
# Items Needed = SUM( 'Projects'[Item Count] )
Availability = 
var __itemsNeeded = [# Items Needed]
return
if( __itemsNeeded > 0,
    var __itemsAvailable = [# Available Items]
    var __result =
        if( __itemsAvailable >= __itemsNeeded,
            1,
            DIVIDE( __itemsAvailable, __itemsNeeded )
        )
    return
        __result + 0
)

 

 

Best

D

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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