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
AvPowerBI
Post Patron
Post Patron

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

1 ACCEPTED SOLUTION
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. 

View solution in original post

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.