topic Re: With a slicer and selectedvalue I use a switch. But is there also an option without the slicer in DAX Commands and Tips

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

Invisibleman — Sat, 29 Apr 2023 21:44:43 GMT

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

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

tamerj1 — Sun, 30 Apr 2023 15:10:12 GMT

Hi Invisibleman

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

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

Invisibleman — Sun, 30 Apr 2023 15:45:59 GMT

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

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

tamerj1 — Sun, 30 Apr 2023 19:17:59 GMT

Invisibleman

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

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

Invisibleman — Mon, 01 May 2023 14:55:27 GMT

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

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

tamerj1 — Mon, 01 May 2023 16:21:08 GMT

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"?

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

Invisibleman — Mon, 01 May 2023 19:32:44 GMT

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

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

tamerj1 — Mon, 01 May 2023 19:53:37 GMT

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.

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

Greg_Deckler — Mon, 01 May 2023 20:01:36 GMT

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

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

tamerj1 — Mon, 01 May 2023 20:05:05 GMT

Greg_Deckler

Wow! That was such a super fast response 😅

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

Invisibleman — Mon, 01 May 2023 20:33:48 GMT

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

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

Invisibleman — Wed, 03 May 2023 07:46:23 GMT

@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

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

Invisibleman — Wed, 03 May 2023 16:22:45 GMT

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

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

Invisibleman — Mon, 29 May 2023 10:41:36 GMT

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