cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jl_c
Frequent Visitor

matrix table filtering issues

Hi all , 

I have two issues regarding my dashboard, attached some photos and a link of sample dashboard

 

Here are my issues

 

 

1) Why does the matrix table sub rows not filter according to the center table in figure 1

 

(fig1)debug.PNG

 

I would like the sub rows to be filtered similar to the below table(fig2)

Why does the matrix table sub rows not filter according to the center table in figure 1

(fig2)

debug 2.PNG

 

 

2) Why does the budget values expansion of sub rows not tally to the values when i collapse the expansion---> please see attached link for the visual

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

 

debug 3.PNGhighlighted expansion does not tally up to 36,509

 

Thanks for help

 

 

 

 

 

 

1 ACCEPTED SOLUTION
sayaliredij
Solution Supplier
Solution Supplier

Yes, you are right. It looks like the budget source is missing some rows from actual source.

 

In this case - you can do following steps

 

1. Click on Transform data

2. Append table_a with table_b.

 

sayaliredij_0-1617698041731.png

 

3. Remove Duplicates using the following option

sayaliredij_1-1617698112329.png

then use Table_A in your model

 

View solution in original post

3 REPLIES 3
sayaliredij
Solution Supplier
Solution Supplier

Yes, you are right. It looks like the budget source is missing some rows from actual source.

 

In this case - you can do following steps

 

1. Click on Transform data

2. Append table_a with table_b.

 

sayaliredij_0-1617698041731.png

 

3. Remove Duplicates using the following option

sayaliredij_1-1617698112329.png

then use Table_A in your model

 

View solution in original post

sayaliredij
Solution Supplier
Solution Supplier

I think there are some improvements needed in your model. 

 

 I don't think we need separate dimensions for Budget and Actuals. You have to connect GL Account, Broad_cat the same way you are connecting Calendar table.  The only table is sufficient. 

 

sayaliredij_0-1617636434829.png

 

I didn't check everything but now stores values make sense

 

sayaliredij_1-1617636532460.png

 I would say its is applicable to all the dimensions which are created using _a and _b - There should be one dimension which is controlling both tables actuals and Budget

 

If they do not belong to same source, you can combine both dimension in power query and prepare just one table from them and connect as above

jl_c
Frequent Visitor

I tried changing the model as you suggested, yield the same results as you , numbers do tally now BUT

i realised that there seems to be an empty rows with values like in your second photo(first row under stores blank category , with value of 2287).

Do the rows arise because one of the sources does not have it in them?  How do i deal with this empty row issue

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors