Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Would you please clarify further perhaps with some screenshots and a couple of well illustrated examples?
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
The subject is clear. What is not so clear is your requirement. What exactly are you trying to accomplish?
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
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"?
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
would you mind help out on this. I'm having difficulties understanding the requirement. Feeling a little bit embarrassed. Appreciate you valuable input.
@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
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.
Thanks in advance
Hans
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
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
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;
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |