Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
After I set up my star schema I noticed that my fields in my dim tables when placed as page level filters have way too many values some that do not exist in my fact table. To tackle this I go back to power query and I do inner joins to reduce the number of rows being returned in my dim tables. My question is, is this good practice, if not is there a better solution.
Solved! Go to Solution.
You can do it that way and that is perfectly acceptable. If you have less than 1,000 things to filter, this artcle explains a different way to do it. Using List.Contains To Filter Dimension Tables — ehansalytics
You can also create a new measure in your model called [Fact Table Record Count] that would simply be COUNTROWS('Your Fact Table'). You can then add that measure to your visuals (slicers for example) and set that measure to not be blank in the filter pane. Even if you prefiter the data at the source or in Power Query, I still do this. That way when one set of data is filtered from Slicer A, Slicer B is automatically filtered for relevent data in your fact table.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can do it that way and that is perfectly acceptable. If you have less than 1,000 things to filter, this artcle explains a different way to do it. Using List.Contains To Filter Dimension Tables — ehansalytics
You can also create a new measure in your model called [Fact Table Record Count] that would simply be COUNTROWS('Your Fact Table'). You can then add that measure to your visuals (slicers for example) and set that measure to not be blank in the filter pane. Even if you prefiter the data at the source or in Power Query, I still do this. That way when one set of data is filtered from Slicer A, Slicer B is automatically filtered for relevent data in your fact table.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks ill try both the ways, but the problem i am getting using merging is performance related issues.
Merging in Power Query can bog down with millions of records or with a lot of steps before/after the merge. So I feel your pain.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting