Creating a dynamic table based on on-screen filters

[SWeijman]

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

[Anonymous]

Here's the full solution.

First, the model:

The model

Then, the report:

The 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