Forum Discussion

KaroRoza's avatar
KaroRoza
Helper I
1 year ago
Solved

FILTER OUT null values

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:

 

 

 

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      KaroRoza
      Helper I
      1 year ago

      Could you please write it step by step?

      What columns should I have in the table?

       

       

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.