Forum Discussion

Maieev's avatar
Maieev
Icon for Resolver I rankResolver I
2 years ago
Solved

Slicer is excluding blank rows in matrix

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. So...
Need Help
  • Maieev's avatar
    Maieev
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Maieev
Icon for Resolver I rankResolver I
2 years ago

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.