Calculated Field for Slicer

[mpeters1988]

Hi everybody,

I'm trying to find out whether or not Power BI allows you to create a column name and use that as the actual Field in a Slicer. As a test I created the following tables/datamodel:

DataFields tableDatamodel

Now I've tried to create a calculated column/measure, based on a SWITCH statement (see example) that contains all names from the  'Fields' table and all column from the 'Calendar' table, but I'm running into multiple errors, even though it looks like everything should be valid.

example:

DynamicColumn = SWITCH(
DataFields[FieldNames];
"ISO_WEEK_NUMBER"; WeekCalendar[ISO_WEEK_NUMBER];

"MONTH_VALUE"; WeekCalendar[MONTH_VALUE];

"None"

)

Has anyone tried this before? I couldn't find any related posts, mainly because it's quite hard to define in a search argument.

EDIT: The reason I'm trying to do this is to dynamically fill a single slicer so a user doesn't need dozens of slicers on one page, but only two: one to select the field to filter, one to do the actual filtering/slicing.

I'm not aware of any reason why this shouldn't be possible in Power BI, but I might be wrong. As far as I know this is a possibility in QlikView and it's a great way to save screen space. I'm open to any other tips/suggestions on how to do these kind of things in Power BI.

Thanks in advance!

Maarten

[v-ljerr-msft]

Hi @mpeters1988,

What's this calculate field used for? Could you be more precisely with your expected result? 

In addition, not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

Regards

[MattAllington]

It is not clear from your post how your tables are structured. E.g. Your calendar table description makes sense but then the fields table description seems to use some different design, and it is not clear what columns it has. You haven't provided I formation about how your tables are joined, if at all. Without this information it is impossible to give a formula that will work. 

It is not clear if you actually need this column - power bi is designed to allow table to work together without the need to consolidate columns. 

[mpeters1988]

@MattAllington: I've edited my post with an example report I made in Power BI, so I hope that sheds some light on what I'm trying to do!

All I'm trying to do is to fill a slicer dynamically based on user selections.

@v-ljerr-msft: So if I understand correctly the only way to do this would be a measure and from what I understand, a measure can only return a single value and there's no way a measure or a calculated column can be evaluated so it can dynamically show a different field in the slicer, even though it is possible to construct the name of that field?

[v-ljerr-msft · Accepted solution]

Hi @mpeters1988,

The reason I'm trying to do this is to dynamically fill a single slicer so a user doesn't need dozens of slicers on one page, but only two: one to select the field to filter, one to do the actual filtering/slicing.

---

In your scenario, you should be able to use the Unpivot option in Query Editor to unpivot those columns(the value type has tp be the same) that you want to use as Slicers.

After unpivoting, you'll get a Attribute column(Field names), Value column(Values). Then you can use only these two columns as Slicers on the report. For more details about how to use the Unpivot option in Power BI, you can refer to this article. 

Regards