With a slicer and selectedvalue I use a switch. But is there also an option without the slicer

[Invisibleman]

Dear all,

For my project, I want to create 1 measure, that will need to calculate based on a variable value. With a slicer, I use the switch( command and selectedvalue to create a variable and then the formula I need. For this I have the table with the selections 1. Current Year, 2. Previous Year, 3. All Years

But now I want to have a switch function without the slicer. where the variable in the formula will be Variable.

Let's say I have the measure with the formula Check the data = calculate( calendar[CurrentYearYesNo] = Variable.

In Another measure I then want to have something like GoToChecktheDate = Variable = "Current Year", 'Check the Data' or maybe something like GoToChecktheData = Check the Data [Current Year]

Is there any option/posibility in power bi to do so?

Thanks in advance,

Hans

[tamerj1]

Hi @Invisibleman 

Would you please clarify further perhaps with some screenshots and a couple of well illustrated examples?

[Invisibleman]

Hello tamerj1,

I am sorry, but I can't get a screenshot, as I don't get it to work, as I want. But I make some more explanation in the word document. Maybe this will also help. To write all down in this tipic, I think it will be too long.

See here my word document with more details; Word document for explanation 

Thanks in advance,

Hans

[tamerj1]

@Invisibleman 

The subject is clear. What is not so clear is your requirement. What exactly are you trying to accomplish?

[Invisibleman]

Hello Tamerj1,

What I do try to achieve, is to get a sort of slicer selection without the use of a slicer. So I am in general looking for some sort of a variable that will work thru out the whole project. And not like the VAR which seems only to be working inside a measure and not outside.

So I can make the coding shorter, easier and I guess it will also make it working faster.

Regards,

Hans

[tamerj1]

@Invisibleman 

I have a feeling that what you want to accomplish is not that complex. Yet, I still cannot figure out what exactly are you trying to describe. What do you mean by "to get a sort of slicer selection without the use of a slicer"? 

[Invisibleman]

Hello Tamerj1,

What I mean by this, is when you have a slicer and you select 1 button, then you can do a switch call from there. As you have the slicer's value by the selection of the button. Like you have the 3 buttons; MTD, QTD & YTD, when pressing the YTD button you can use the SELECTEDVALUE and call the switch function to calcuate the YTD command that you have set. 

But I don't want to have a button to press, I want to do thru a measure. Which will work as if you pressed a button YTD (without actually have such button), but not actually pressing one.

I hope this will help.

Regards,

Hans

[tamerj1]

@Greg_Deckler 

would you mind help out on this. I'm having difficulties understanding the requirement. Feeling a little bit embarrassed. Appreciate you valuable input. 

[Greg_Deckler · Accepted solution]

@tamerj1 @Invisibleman Here's my best guess on this one. Create a disconnected table with the text values, 1 per row of:

Column1

YTD

QTD

MTD

Let's call this table "NotASlicer". In your measure, then you would do this:

Measure =
  VAR __NotASlicer = MAX('NotASlicer'[Column1])
  VAR __Result = 
    SWITCH( __NotASlicer,
      // put some code here
    )
RETURN
  __Result

[Invisibleman]

Hello Greg,

I have now a problem with the formula and I have no idea why/how to solve.

I am trying to have the same situation, but with multiple selections, and also not the MTD, QTD, YTD, but here it is always showing the me the highest value/name. So I can't make the selection as it is in your example, which does work.

Can you also please help me with this? I have added the excel file, I used for the data and also the test Power BI, with your formula (MTD, QTD & YTD) and also mine.

The Excel file with the data 

The Power BI File 

Thanks in advance

Hans

[Invisibleman]

@Greg_Deckler 

I do understand the code you send to me and also what is happening there. Thanks for that. But untill now I still don't understand, in the way I can get this to work from another measure (without the use of any slicer or such). I am trying to find the answer on the internet, but there they all are speaking about the VAR function inside a measure and not in the way how I can address this VAR measure from another measure assigning the value to the VAR measure function. In your example the ____NotASlicer.

As example I have the measure; SendValueToVAR and I want to have the VAR calculate the QTD, how do I write that code? 

I can't use measure["QTD"] as when I want to addres the measure I only can say measure[Column1]. So here I am very stuck. Can you please help me with that part too??

Thanks in advance,

Hans

[Invisibleman]

Hello Greg,

Thanks for the measure. But I don't understand, how I will need to call this measure, from another measure, to get the MTD, QTD or YTD which ever I at that moment need. Or can I just call it like measure["MTD"], Measure[QTD] or measure[YTD] depending on that what I need?

I'm sorry, I am trying to understand, as I am still somewhat new to this 🙂

Regards,

Hans

[Invisibleman]

Hello Greg,

I found it out, and in general very easy and also logical. But I didn't think about this. But all you need to do is a measure and use a filter. For testing I had created a simple measure and a simple measure to get the result;

Measure =

  VAR __NotASlicer = MAX('NotASlicer'[Column1])

  VAR __Result =

    SWITCH( __NotASlicer,

      "MTD","MTD Is being selected",

      "YTD","YTD Is being selected",

      "QTD","QTD Is being selected"

    )

RETURN

  __Result

And the test measure I have;

Measure 2 = CALCULATE(

    'NotASlicer'[Measure],

    'NotASlicer'[Column1] IN { "QTD" })

I'm sure you know this, but I mention this, as maybe someone else is also looking for such.

I want to thank you both Tamerj1 and Greg for the help.

Regards,

Hans

[tamerj1]

@Greg_Deckler 

Wow! That was such a super fast response 😅