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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Filter Doesn't Works

My filter does not show the dataset options, even with the dimension table correctly classified. What is stranger is that the options appear from a date, and others do not.
The fields selected in the table are: Store_ID (fact table), Store_Name (dimension table). The field selected in the filter is StoreName (dimension table)prob2.PNGprob1.PNG

1 ACCEPTED SOLUTION

This can be a number of things.

  • You have blank values it seems in your DIM table. That is never good. You shouldn't have data in your FACT table not in your DIM table unless you intend to, and then you will be using different measures for calculating values, and you cannot use filters/slicers efficiently since those don't exist in the DIM table.
  • Could be spelling differences, or spaces. Is it "Shopping" in one table and "Shopping     " in your other table?
  • I am not clear on how that bi-directional many-to-many is affecting the model for these particluar fields.

 

There could be other issues too. Without the PBIX, just tossing out things for you to look at.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

Your FACT table isn't (shouldn't) be filtering your DIM tables. You need to put your Store ID from the DIM table it is in. Never use fields in your FACT table for filtering, slicing, etc. In fact, I generally hide all fields in the FACT table not directly used for reporting, and that is usually just text fields. Any numerical fields I convert to an explicit measure to prevent any implicit measures. Then I hide all of the numerical fields.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

The same is true when using the dimension table ID_Store. The problem is not there.

Anonymous
Not applicable

prob3.PNGI really don't know what's going on here. The problem does not seem to be exactly with the filter. Look at these tables:
table 1 is the Operation (dimension table) and the store id (fact table). Table 2 contains only the operation (dimension table). Although in table 1 there are stores with the classification "shopping", this option does not even appear when I select only the type of operation in another table.

I'd need to see an image of the model and prefereably a link to a PBIX file. Screenshots of slicers and visuals dont' really tell me how they are connected.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I cannot send the file because this data is confidential. But here's the model. (id_etb_gcom = Id_loja)prob4.PNG

This can be a number of things.

  • You have blank values it seems in your DIM table. That is never good. You shouldn't have data in your FACT table not in your DIM table unless you intend to, and then you will be using different measures for calculating values, and you cannot use filters/slicers efficiently since those don't exist in the DIM table.
  • Could be spelling differences, or spaces. Is it "Shopping" in one table and "Shopping     " in your other table?
  • I am not clear on how that bi-directional many-to-many is affecting the model for these particluar fields.

 

There could be other issues too. Without the PBIX, just tossing out things for you to look at.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable


It worked, and it was for something related to point three of what you mentioned.

I have no idea why, but the fact that there is a many-to-many relationship between D_Store and the second fact table was messing up the relationship with the first fact table as well. When I canceled the many-to-many relationship with the second fact table, everything worked normally.

 

Thank you, bro!

Glad you got it sorted out @Anonymous.

I'm pretty good with Power BI, but even I will avoid Many-to-Many relationships wherever possible. It is different beast and can have odd effects on the model. You need to fully understand what it is doing. I'm not to that chapter in The Definitive Guide for DAX yet😀



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.