FILTER OUT null values

[KaroRoza]

Hello,

Could you please help me with the problem? 

I would like to add Filter/Slicer to the dashboard to allow User to filter out table depending if he want to see also null rows.

'TP USD' is measure CALCULATE( divide(SUM GOR), SUM(Units)). I have table with Countries, Years, Products sold. If there are no sales from Factory at that year there is no 'TP USD',  only measure 'SalesPrice1). 

In Filters panel I can filter it out as 'is not blank' but I want User to decide if he wants to see all or only Products with both Factory ans Sales price. Filters panel is hidden.

When I add measure 'TP USD' to slicer it is not allowed, but I want to get something like slicer with option: 'ALL', 'Both Factory and Market".

Could someone help me please.

to get:

[johnt75 · Accepted solution]

Use the Enter Data functionality to create a disconnected table which you will use in the slicer.

You can then create a measure to use as a filter

Row is visible =
IF (
    SELECTEDVALUE ( 'Slicer Table'[Value] ) = "All",
    1,
    IF ( NOT ISBLANK ( [TP USD] ) && NOT ISBLANK ( [SalesPrice1] ), 1 )
)

Add this as a filter to your matrix visual, to only show when the value is 1.

[Anonymous · Accepted solution]

Hi, @KaroRoza 

I've simply modeled some data that hopefully fits your situation. As @johnt75  says, first create a slicer table.

Measure = SWITCH(TRUE(),
SELECTEDVALUE('Slicer Table'[Value])=BLANK(),1,
SELECTEDVALUE('Slicer Table'[Value])="All",1,
SELECTEDVALUE('Slicer Table'[Value])="Both Factory and Market"&&[TP USD]<>BLANK(),1
)

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[johnt75 · Accepted solution]

Use the Enter Data functionality to create a disconnected table which you will use in the slicer.

You can then create a measure to use as a filter

Row is visible =
IF (
    SELECTEDVALUE ( 'Slicer Table'[Value] ) = "All",
    1,
    IF ( NOT ISBLANK ( [TP USD] ) && NOT ISBLANK ( [SalesPrice1] ), 1 )
)

Add this as a filter to your matrix visual, to only show when the value is 1.

[KaroRoza]

Could you please write it step by step?

What columns should I have in the table?

[johnt75]

Create a new table with the expression

'Slicer Table' = { "All", "Both Factory and Market" }

That will create a table with a single column called "Value".

Add the Value column to a slicer.

The measure should now work if you use it as a filter.