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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 Sage
Solution Sage

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 Sage
Solution Sage

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

 

sayaliredij
Solution Sage
Solution Sage

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

Anonymous
Not applicable

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.