Slicer leaves the unselected values blank in matrix

[Anonymous]

Hi all, I have 2 different data sources with one common column X. I have created a bridge table to join to the 2 data sources separately. When I use the common column X on the report to filter one of the tables (matrix), for example, filter on education = "university", what I end up getting is values on the row where education = "university", however, the rest of the rows are still there, filled in with blanks. Please advise on what might be causing the issue, normally a slicer filters out the values that are unselected, not sure what's the case here.

Thanks in advance. 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Have you used any measure formulas who include the internal filters to force expand the calculations on filtered ranges?
In addition, it will help if you provide more detailed information about your scenario.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[Anonymous]

solution: I ended up using a IF statement to format the values when it is not blank. 

[edhans]

Just an FYI @Anonymous - You might look at the COALESCE function. Performs the same as IF, but a bit cleaner when you are looking to eliminate blanks.

It returns the first non-blank value in a list of values, and then returns the last value if all of the rest are blank.

Measure = 
VAR varRows = COUNTROWS(table)
RETURN
COALESCE(varRows,0)

 That returns 0 if there are no rows, and thus blank. 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Have you used any measure formulas who include the internal filters to force expand the calculations on filtered ranges?
In addition, it will help if you provide more detailed information about your scenario.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi,

After a few testing with my measure. You are absolutely right, I believe the format funciton in my formula is driving this issue. 

However, I need the formula funciton in my measure as I am returning both actual $ and % change in my formula. Is there a way to go around this?

Thank you.

Regards,
Veronica

[Ashish_Mathur]

Hi,

Ensure that the common column X is dragged from the bridge table.  If it still does not help, then share the link from where i can download your PBI file.

[edhans]

Is your slicer using the bridge table columns, or one of the Column X from the two fact tables? It should use the common bridge table Column X, where that is filtering both fact tables in a 1:Many relationship.

Can you show a pic of the model and the slicer settings?

[Anonymous]

Thanks for your kind reply. I am using the common bridge table column and it is 1:many to both of the 2 tables.