Help with matrix visual filtering

[mp390988]

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

[v-venuppu]

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]

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.

[mp390988]

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]

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]

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.

[mp390988]

Hi,

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

[MasonMA]

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

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. 

[SundarRaj]

@mp390988,

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