Switch between view all or only with values

[rpinxt]

Must be very simple but cannot get it to work....

Have this simple table:

Now I want to make a slicer that shows me only the periods where there are actuals.

Or switch the other side to show also the zeros.

Was trying with If / Calculate and Sum but can only get a Yes (active month) or No (zeros) when I put the calculated field in the vsiual.

But I want it in a slicer.

[Anonymous]

Hi @rpinxt ,

Use enter data feature to create a slicer table:

Then create a measure like below and add it to visual filter.

Measure =
SWITCH (
    SELECTEDVALUE ( slicer[slicer] ),
    "yes", IF ( SELECTEDVALUE ( 'Table'[ActualsLC] ) > 0, 1, 0 ),
    "no", IF ( SELECTEDVALUE ( 'Table'[ActualsLC] ) = 0, 1, 0 ),
    1
)

Best Regards,

Jay

[rpinxt]

Very Nice one @Anonymous but also this one I cannot not use in a simple slicer apparently....

What am i doing wrong?!

This is my measure:

ActivitySelection =

SWITCH (

SELECTEDVALUE ( Activity[Activity] ),

"Yes", IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) > 0, 1, 0 ),

"No", IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) = 0, 1, 0 ),

1

)

Made this table:

I also cannot put this measure in my filters...just does not take it...

What is different in my measure, table setup??

[Anonymous]

Hi @rpinxt ,

The measure should be added to the table visual not the slicer visual.

If it still doesn't work, please share the pbix file.

Best Regards,

Jay

[rpinxt]

Ok @Anonymous , somehow now was able to put ActivitySelection measure to the visual filter as in your screenshots.

But you also have a slicer there with yes and no.

How did you then make that?

Because when I make a slicer and want to put the measure in it will not take it.

*EDIT*:

Duhhh...my bad!

Had to use the normal Activity not the measure....😣

Just not getting it to work...

When I select Yes all is gone....hmmm have to check it out more

[Anonymous]

Hi @rpinxt ,

The slicer is used to return different value for the ActivitySelection measure.

ActivitySelection =

SWITCH (

SELECTEDVALUE ( Activity[Activity] ), --> get the selected value from slicer.

"Yes", --> when "yes" is selected.
 IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) > 0, 1, 0 ), --> return 1 when [ActualsLC]>0, otherwise return 0.

"No", --> when "no" is selected.

IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) = 0, 1, 0 ), --> return 1 when [ActualsLC]=0, otherwise return 0.

1 --> always return 1 when nothing was selected in the slicer

)

Then put the measure to visual filter to only show the records have measure = 1.

If you want to show "yes" or "no" for each records, just create another measure like below and add it to table visual.

measure2 = if(selectedvalue(SAP_Data[ActualsLC])>0,"yes","no")

Best Regards,

Jay

[rpinxt]

Ok thanks for the explanation!

But something must be off then.

My formula:

With nothing selected in slicer:

With No selected:

With Yes selected:

The filter in the visual:

The measure shows always 1 and I think my logic is also the wrong way around?

Would expect 1 when there is a value.

[Anonymous]

Hi @rpinxt ,

That's weird, can you share the pbix file? 

Best Regards,

Jay

[rpinxt]

How would I do that?

Tried dragging and dropping it on this reply but says .pbix is not supported.

Never shared a file before via this site.

[Anonymous]

Hi @rpinxt ,

Do you have google drive or onedrive? You could upload the pbix file and share the download link here.

Best Regards,

Jay

[rpinxt]

Doubt a bit if this will work.

Link from my onedrive but not sure if it will work....

https://1drv.ms/u/s!AgfrQ7oUAQcfuQLNgWJm7hlrdKK2

[rpinxt]

Maybe more luck with my google drive link:

https://drive.google.com/file/d/1m1_Q1feoHOJMacreUPQRv_6gLBGf05so/view?usp=sharing

[Anonymous · Accepted solution]

Hi @rpinxt ,

Sorry for the delay.

I tried using "SUM ( SAP_Data[ActualsLC] )" instead of "SELECTEDVALUE ( SAP_Data[ActualsLC] )" and it works.

ActivitySelection = 
VAR selection =
    SELECTEDVALUE ( Activity[Activity] )
RETURN
    SWITCH (
        selection,
        "Yes", IF ( SUM ( SAP_Data[ActualsLC] ) > 0, 1, 0 ),
        "No", IF ( SUM ( SAP_Data[ActualsLC] ) = 0, 1, 0 ),
        1
    )

This is because the [ActualsLC] here is a sum value and not a single value.

Pbix as attachment.

Best Regards,

Jay

[rpinxt]

Thanks! @Anonymous 

It works now indeed.

I will be studying this file to understand exactly how it works (most of it I already understood)

Thanks for all your time and effort!

Ps: how did you add the pbix as an attachement??

I could not find that option on my side.

[rohit_singh]

Hi @rpinxt ,

It is simple indeed. 

1) Add a flag to your table to check if the actuals column has a value or not. If it is 0 then 0 else 1. Name this "ValueFlag"

2) Add your period and actuals values into a table and add valueflag as a slicer. Set value of valueflag as 1.

When you select 1, it wil show you values

When you deselect the slicer, it shows all values

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[rpinxt]

This is an interesting one but somehow does not work for me:

In the table it works.

But in the slicer nothing shows up.

And when in the filer I cannot put in on advanced filtering.

Also..would this only work with 0 and 1?
Not like with Yes/No or On/Off ?

[rohit_singh]

Hi @rpinxt ,

Please use the slicer visual that I've circled in red in the screenshot below

You can change the calculated value to show whatever you feel like. I've chosen 0/1. Just be sure to set the filter on the slicer using the same value ( I set it to 1 in my case, you can set it to Yes or On etc depending on the values you choose)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  🙂

[rpinxt]

ah only with the standard slicer? Hmmm...but i cannot put the measure that I made to that slicer at all....

Is there something wrong with my formula? It is working in the table:

[rohit_singh]

Hi @rpinxt ,

ValueFlag needs to be a calculated column NOT a measure. Only then can you use it in a slicer.

Kind regards,

Rohit

[jcalheir]

Hi,

You can create a new column that says if your actuals are Zero or not

Actuals = If(Table[ActualsLC] = 0, "Zeros","Actuals")

Then you add your new column to a slicer and you can filter that way