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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.