Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 @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.
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:
fact table B contains the following fields:
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
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,
Simply right click on the measure and select "Show items with no data". That should work. Thanks
Here, the Dealer ID row is dragged inside and text wrap is off.
Hi,
This does not work when dealer name has to be the first column and then dealer id follows after dealer name.
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.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
74 | |
71 | |
38 | |
29 | |
26 |
User | Count |
---|---|
97 | |
96 | |
60 | |
44 | |
41 |