Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mp390988
Helper IV
Helper IV

Help with matrix visual filtering

Hello,

 

I have a table like below called Monthly Targets

mp390988_2-1753015994975.png


Then I have another table from a sql source that contains the following data call it Trades

 

mp390988_1-1753015959494.png

 

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

 

8 REPLIES 8
v-venuppu
Community Support
Community Support

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.

danextian
Super User
Super User

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.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

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


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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 

FBergamaschi
Solution Specialist
Solution Specialist

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

SundarRaj
Super User
Super User

Hi @mp390988,

Simply right click on the measure and select "Show items with no data". That should work. Thanks

SundarRaj_0-1753017898920.png


Here, the Dealer ID row is dragged inside and text wrap is off.

Sundar Rajagopalan

Hi,

 

This does not work when dealer name has to be the first column and then dealer id follows after dealer name.

MasonMA
Impactful Individual
Impactful Individual

@mp390988 

 

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. 

MasonMA_0-1753045646308.png

 

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'. 

MasonMA_3-1753046687864.png

 

MasonMA_1-1753045924415.png

With 'Show Items with no data' is enabled, you will see 

MasonMA_0-1753046880241.png

 

Here's a demo file for your reference. 

 

@mp390988,

Is this what you are looking for? I'll share the link to the file as well. Thanks

SundarRaj_0-1753045315412.png

 

https://drive.google.com/file/d/1lrR0A53kVmiA1bUcd9Vh_duYm9lqTzo6/view?usp=sharing

Sundar Rajagopalan

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.