cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AvPowerBI
Helper V
Helper V

Only load matching results for the Dimension Query

Hi,

 

I am currently loading two queries, one is a Transactional Fact table i.e orders, the second query is a Dimension of a list of Customers;

the relationship between the two is CustomerId.

I only want when loading the Customer Query to load only the ones where there has been a Order on the Fact Table otherwise when I put the Customer Name on the filter pane it is show all customers when they haven't made a transaction and is confusing the end Customer as they think that Customer has made a Sale.

 

Thanks

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @AvPowerBI ;

You can write a measure, use filter to add conditions to filter out the transaction order, and then use if... In just filter table then 1, then apply to filter on Visual.

 

If not, can you share more details and screenshots about your table structure and what you want to output?

 

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson
Super User
Super User

You can do an inner join the fact table to the customer table in the query editor but you could also use a measure filter on the slicer.

 

For example, define a measure

HasData = IF ( ISEMPTY ( FactTable ), 0, 1 )

and then set [HasData] = 1 as a visual level filter on your slicer. 

smpa01
Super User
Super User

@AvPowerBI  before loading, in PQ/RDBMS filter dimension by fact (innerjoin dimension with fact)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


The Fact table is coming from a SQL Server Query and the  Customer dimension is a spreadsheet, how would I be able to do this then?

Even if they're separate sources, you can do an inner join in the query editor before loading the tables into your data model.

 

Tip: After doing the merge (inner join), remove the new column it creates rather than trying to expand it. You're only merging in order to filter your dimension table and don't need to bring over any columns.

@AvPowerBI   in that case please follow what @AlexisOlson is suggesting because if you have multiple dimensions, it might not be practical to inner join each of the dimensions by fact. So you can create a measure and do the filtering there.

 

If you absolutely don't want any extra dimensions to load, then you have no other choice to do that in PQ pre-load ( or in dataflow if you are on premium workspace)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors