Allow End User to Set Value of Column Equal to Another Column

[zebing2]

I've created a new column called [New_Column]. I would like to allow the end user to be able to choose to set [New_Column] equal to [Other_Column1] or [Other_Column2] by using a drop down list of options. How can I accomplish this?

[zebing2]

[Column1] | [Column2] | [Column3]

        1        |         A       |         B

Maybe this is the question. How do you make it work in DAX?

IF(Column1=1, Column2, Column3)

[v-ljerr-msft]

Hi @zebing2,

[Column1] | [Column2] | [Column3]

        1        |         A       |         B

Maybe this is the question. How do you make it work in DAX?

IF(Column1=1, Column2, Column3)

---

Could you try using the formula(DAX) below to create a measure to see if it works in your scenario? 

Measure =
IF (
    MAX ( 'Table1'[Column1] ) = 1,
    MAX ( 'Table1'[Column2] ),
    MAX ( 'Table1'[Column3] )
)

Regards

[zebing2]

Unfortunately, that doesn't seem to work for reasons that I don't understand.

My actual field is called COMP_STACK_SWITCHER. It changes values depending on a slicer. It is a numberic measure. Here is an image of the error. The field is definitely in the "forecast" table so I don't know why it can't be used in my expression.

Any ideas?

[v-ljerr-msft · Accepted solution]

Hi @zebing2,

If COMP_STACK_SWITCHER is a measure(not a column), then the formula below should work.  

TESTS =
IF (
    [COMP_STACK_SWITCHER] = 1,
    SUM ( Forecast[TY_COMP] ),
    SUM ( Forecast[LY_COMP] )
)

Regards

[MattAllington]

Columns are not designed for user interaction. What are you trying to achieve?

[zebing2]

What I'm trying to accomplish: Switching away from Tableau because Power BI has some better features.

More specifically in my current situation, I'm trying to accomplish this:

https://www.interworks.com/blog/rcurtis/2016/05/26/tableau-deep-dive-parameters-calculated-fields

I used to do this a lot in Tableau. In my current project, I'm trying to allow a user to select different variables to visualize. I'm trying to use the R tool to plot a particular measure and do a regression on it. Let's call it [Sales]. I'd like to set up r to do regressions on a field called [New_Column], but allow the end user to choose whether [New_Column] is populated with sales data or profitability data. That way they can choose to see a regression on sales or profitablity with just a few clicks.

I realize I can just update the R code which is just fine when I am the user, but when sending this tool off to an executive, I can't just tell him to pop open the R tool and adjust the code. I aslo don't like the idea of giving him 9 different charts- one for each variable (I have more than just the 2 variables in my example). I need an easy way to allow him to choose what regression he will see.

Can Power BI do it?

[v-ljerr-msft]

Hi @zebing2,

---

---

In my current project, I'm trying to allow a user to select different variables to visualize. I'm trying to use the R tool to plot a particular measure and do a regression on it.

Where do you use the R script, in Query Editor, or in the R script editor of an R visual?

Is it possible that you can do the regression with DAX instead of R?

I realize I can just update the R code which is just fine when I am the user,

---

If you're doing the the regression with R in the R script editor of an R visual, you should be able to firstly add a new table with a column called "Regression Type"(contains values like "Sales", "Profitability ") and use the "Regression Type" column as Slicer on your report. Then you should be able to use R to get the selected value, and use if r function to determine which calculation you need to do in your R code.

Regards

[zebing2]

Alright, so the post above is generally correct. But to point out something, I spent several hours googling and trying to figure out how to do this last week and couldn't find a post that fully explained what needed to be done. I bought a book on power pivot and read it over the weekend and have been able to accomplish this now, but someone out there should probably write something a bit more complete on how to get this to work. Talbeau's tutorials are pretty clear, and Microsoft, it turns out, does have this functionality. Just haven't arrived at the point of having it explained well.

I'm a bit too busy to do something like that, so sadly can't really contribute. But some giving soul out there really should do a good tutorial on dynamically replacing measures with other values and it's many uses on enabling user interactivity. People in my office seem to believe Power BI is limited in functionality, which I think is wrong. Just limited in explanations at this point.

[MattAllington]

Do you mean this?  https://exceleratorbi.com.au/measures-on-rows-here-is-how-i-did-it/

Or something else?

[zebing2]

First, someone should give you a ribbon or something for writing all of that. That is cool. Couldn't find something like that after a half day of searching the internet. I had to buy a book to find this stuff out.

I'm still hitting problems though. Instead of replacing with measures in the SWITCH statement you show, I'm trying to swap in and out a column that is a text column. I've done this in Tableau and it worked fine, but the idea is when I swap in a new value of text, it groups my data differently. Imagine having a table and being able to use a slicer to see each line at department or category level. I know power bi let's you drill in on some reports, but I have a line chart and it doesn't seem to allow that. So I'm trying to let the end user choose to see separate lines for each department or each category depending on what they choose to populate the switch column with.

To boil the question down even further, I can't seem to be able to set a measure equal to a column that has text strings in it. I think that's probably the main issue.