Forum Discussion
Help with matrix visual filtering
Hello,
I have a table like below called Monthly Targets
Then I have another table from a sql source that contains the following data call it Trades
The problem I am having is that not all the dealer ids from the Monthly Targets appear in the Trades table and as a result, I am misreporting. For example, Seasonality is in the Monthly Targets but not in Trades table so whenever I create a matrix visual in power bi and I drop the Dealer ID from Monthly Targets into the rows section it is showing all dealer ids from Monthly Targets but as soon as I drag and drop say Dealer Name or Dealer Group from the Trades table also into the rows section, it drops off the values that it cannot find in the Monthly Targets source. I want to show all dealer ids from the Monthly Targets table irrespective of whether they appear in Trade table or not.
How do I go about this?
Thank You
Hi, Thats's because your Dealer Name comes from the Trades table (the fact table) and Dealer ID comes from the Monthly Targets table (might be the dimension table)
Even though “Show items with no data” is enabled on Dealer ID, when the first column in the matrix (the row group) is from the fact table (Trades), Power BI falls back to inner join behavior, which only shows matches.
Part of your model was likely built in this way below.
If in your situation, I would create one Dealer Dimension table in Power Query by appending all distinct dealer info from two tables, then have it filter 'Trades' and 'Monthly Targets'.
With 'Show Items with no data' is enabled, you will see
Here's a demo file for your reference.
10 Replies
Hi,
This does not work when dealer name has to be the first column and then dealer id follows after dealer name.
Is this what you are looking for? I'll share the link to the file as well. Thanks
https://drive.google.com/file/d/1lrR0A53kVmiA1bUcd9Vh_duYm9lqTzo6/view?usp=sharing
I am not sure I understood the situation
Which of the two is true
A you have Dealer IDs in Tardes that are not in Monthly Targets
B you have Deeale IDs in Monthly Targets that are not in Trades
?
To help you more: the suggestion is to create a full list of Dealer ISs, from both tables and use it as a dimension filtering both tables, you can create a new table with this code
Dealers =
DISTINCT (
UNION (ALLNOBLANKROW ( 'Monthly Targets'[Dealer ID] ),
ALLNOBLANKROW ( Trades[Dealer ID] ))
)If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi mp390988
How is the relationship setup? Since they are both fact tables, I would assume there is a separate dimensions table that covers all the distinct Dealers IDs from both table. If not, FBergamaschi already has suggested a calc table formula. Create a one-to-many single direction relationship from this dim table to the fact tables. Use the column dim table in the visuals instead of the ones from the facts.
Hi danextian,
Ok, so I have two Fact tables.
fact table A contains the following fields:
- Dealer Group
- Dealer Name
- Dealer ID
- Trade Date (i.e. 16 July 2025)
- Trade Month YYYY (i.e. July 2025)
- Revenue
fact table B contains the following fields:
- Dealer ID
- Month YYYY (i.e. July 2025)
- Target Revenue
fact table A shows all the trades across each dealer and the revenue generated from each trade and fact table B just shows the monthly target for the dealer based on dealer id for each month of the year.
So we cannot join the two tables together based on just the dealer id as it will create a many to many relationships. Also, table B does not contain Dealer Group and Dealer Name and the idea is to have a matrix visual ordered by Dealer Group, Dealer Name, Dealer ID, Revenue, Revenue Target and then we have a separate slicer for Month YYYY.
is that clear?
thank you
Hi mp390988 ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you danextian MasonMA SundarRaj FBergamaschi for the prompt response.
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
