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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Jagerrrr1234
New Member

Making a dinamic filter with dax

Hi, 

 

I'm kinda new to PBI and i really need to solve the following problem:

 

I have stock data of different stores to the last day of each month, but for some technical reason, there are a few record that has no data. It looks like this: 

 

Jagerrrr1234_0-1660290010014.png

 

I have a date slicer, but I also want to make a dinamic filter somehow (with respect to the date slicer) to filter out those store, where i don't have data for each month. Something like this: 

 

Jagerrrr1234_1-1660291112648.png

 

I tried to figure it out, but it doesn't work, and I dont even know if I'm on the right way...

 

Here's my attempt: 

Jagerrrr1234_3-1660293389529.png

 

If I create a table with this code and return the StoreToFilter variable, this works as I expect, and returns the stores that has data for every monhts, but in the report, I can't figure it out, how to use it as a filter. 

I uploaded the dummy data here: 

https://docs.google.com/spreadsheets/d/1ArDbLjXZZLKo8oA6CJ6NJbY2l1zRAH0j7IdlR1SKTGo/edit#gid=0

 

I hope my explanation was clear, and thank you for the help in advance! 

 

 

2 REPLIES 2
Jagerrrr1234
New Member

Hi @daXtreme 

 

Thank you for your answer! 

 

Can I ask some further help regarding the dax formula? 

 

Thank you!

 

 

daXtreme
Solution Sage
Solution Sage

Hi @Jagerrrr1234 

 

Before you even start to think about a Power BI project, I would strongly recommend that you first of all create a proper model. This means strictly no models with one table. There are too many reasons (and horror stories) to state here why this should be so. If you want to know how to build a good correct model in PBI, please read this: Understand star schema and the importance for Power BI … (bing.com)

 

One-table models are known for issues that can't be resolved when they hit and sadly, you never know when they do. So, please for your own sanity and correct figures create a good model. Then it'll be much easier to reason about it and create correct measures.

 

Once you've got a good model...

 

If you want to exclude stores that have at least one month where a measure is blank, then you have to create an informational measure in which you'll iterate over all the months in scope (using ALLSELECTED) and calculate the number of months where the measure is blank (for the store in context). If the number is > 0, then you return 1, else 0. Then you use this helper measure in the filter pane of the visual to filter out such stores.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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