Slicer is excluding blank rows in matrix

[Maieev]

Hi community,

I got a lot of help in here, so I hope for it again :D.

I have a matrix table with many columns and in Values I have Revenue. I also have slicer to change Currency of values. Some of the rows have no value and I wrote measure like that to keep them in the table:

FilteredRevenue =

    IF(SUM('Sales'[Revenue]) <> 0, SUM('Sales'[Revenue]), BLANK())

as I would like to see if there was or wasn't revenue in past years. Unfortunatelly when I change Currency slicer, blank rows disappear. Can I somehow force slicer to omit blank values and let them be? Currency and Revenue are in the same table - Sales.

Row with fixed price 4,33 and no revenue in past years is missing...

Thank you for any advise!

[Maieev · Accepted solution]

Alright, I found a mistake in data model, the cross filter direction was set to both and should be Single - base table filtering Sales table. It works now.

[Anonymous]

Hi @Maieev ,

Thank you for reaching out for assistance with your Power BI slicer issue. Based on your description, you can follow these steps to solve your problem:

1.You can modify your Dax below.

Measure =

IF ( SUM ( 'Table'[Column1] ) <> 0, SUM ( 'Table'[Column1] ), "" )

2.Add an index column to the table for the purpose of making blank rows appear.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Thank you for your questions and support! If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Maieev]

Hi,

thanks for the reply but it's still not working for me. The formula works only with BLANK()

FilteredRevenue =

    IF(SUM('Sales'[Revenue]) <> 0, SUM('Sales'[Revenue]), BLANK())

and I cannot add index because data in the matrix is coming from two tables. One is showing text and values in rows and second table is table for sales. As I mentioned - for some rows there is no value and they should be still visible in matrix and it works:

but when I want to change currency in slicer it excludes those blank rows. Currency field is coming from Sales table, so where the values are. It should be single select.,

It is logical that it excludes those rows with no values as they don't exist in the Sales table but can I still force matrix to show them when currency changed? Maybe there is a way to write formula that way when I change currency and field exists in the first table to still show it as blank even when in the Sales table it does not exist?

I'm stuck. Please help.