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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

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

 

relationship.PNGI 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,

inout.PNG

 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.

 

 

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

See if this works. Firts, the sample model
model.jpg

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:

Filter.jpg

to get

filter gif.gif

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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!

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors