How can I apply CROSSFILTER or similar method in this model to be able to filter a table?

[balaganeshmohan]

I have been stuck on this modelling problem for last couple of days, I have my model currently setup like this,

I want to filter SKU forecast table with the column Status from INOUTstatuses table. Both are connected to date table and item table with 1 to * relation. An excerpt of inout table looks liek this,

 Each sku has a status with a respective date. What I want to do is, add status column in the filter pane and selected a status for a respective year and it should show all the sku under those two conditions and measures and columns calcuated in forecast table should dynamically change. I cannot use bi-directional filter too in this case. Any help is much appreciated.

[PaulDBrown]

See if this works. Firts, the sample model

To filter Table 2 based on the selection in the filter from the Status table, create the follwoing measure:

Filter Table 2 =
IF (
    ISFILTERED ( 'Status Table'[Status] ),
    COUNTROWS (
        SUMMARIZE ( 'Table 1', 'Table 1'[Status], 'Dim Sku Code'[dSKU code] )
    ),
    1
)

In the visual for Table 2, add the SKU field from the dimension table to the filter pane, select TopN as the filter, add the [Filter Table 2] measure and set the topn to 1:

to get

Sample PBIX file attached

[balaganeshmohan]

Thanks, This look promising, however I've already looked into creating dynamic measure as a filter option, but the problem I have multiple pages and around 20 different visuals, so I need this status filter as a page level or report level one. Is it possible to modify this solution? I have pondered over it, but couldn't seem to think of a way a calculated column that is dynamic!

[PaulDBrown]

I cannot think of a way to make this workable at page/report level. If the solution needs to be dynamic, you are going to need to use a measure, and measures cannot be applied at page/report level filters