Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
@AvPowerBI before loading, in PQ/RDBMS filter dimension by fact (innerjoin dimension with fact)
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)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |