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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
edhans
Super User
Super User

Best way to limit DIM tables to only contain matching records from FACT tables?

I'm used to working in Access where I can do inner joins everywhere, but DAX relationships don't do this. So often my slicers have a ton of useless data - Customers in the DIM tables for example that we haven't sold to in years.

 

Looking for the best way to limit the data in my DIM tables to the data that is in my FACT tables. Our sales report, for example, only uses sales data from the last 4 years for comparisons.

 

  1. Do an inner join in Power Query from my Sales FACT table to my Customer DIM table as one of the last steps before the customer table gets loaded into DAX. This happens after some transformations cause query folding to stop, so this happens 100% in the Power Query engine, and can slow it down, but it works.
  2. Add a calculated column from the customer table that is = COUNTROWS(RELATEDTABLE(Sales)), then drop that in a Page/Report filter, and set it to only show > 0.

#2 is easier, but I am generally loathe to add caclulated columns.

 

Any ideas from others on how you do it?

 



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
3 REPLIES 3
v-cherch-msft
Employee
Employee

Hi @edhans

 

You may try to select ODBC import option, choose the DSN you created and then you may put your sql query into SQL Statement.

 

Regards,

Cherie

 

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Try turning on bidirectional relationships. It comes at a cost (overhead) but maybe it is ok for you. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Yeah... that is definitely a third option. I'm not comfortable with how DAX handles bi-directional. It makes some relationships go inactive, and there is some overhead. It is almost to the point for me I'd rather use a CROSSFILTER() inside a CALCULATE() if I need bi-directional, but of course I cannot put that in a filter, or I would.

 

Thanks though. I'll need to look at my model, but given I want to do this on the customer and product table, I'll probably it some snags in other relationships.



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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