topic Re: Using a Measure to create a new column that can be used to filter in DAX Commands and Tips

Using a Measure to create a new column that can be used to filter

Bernstra — Thu, 23 Jun 2022 21:24:49 GMT

I am working with an Analysis Services dataset, and I do not have access to create a new column. There is a column named "type" in the item table that we need to adjust, and then use to filter the data. If I could create a column, then a couple IF statements and SWITCH work to do exactly what we need, but in this dataset that is not possible. The data would be similar to the table below:

Item #	Group	Type	New Type
1	A	T1	TA
2	A	T1	TA
3	B	T2	TB
4	C	T3	TB

Where the New Type column is the column we would like to create:

Type T1 -> New Type TA

Type T2 -> New Type TB

Type T3 -> New Type TB

Is there a way to do this with a measure and be able to filter the table using the "new type"?

Thank you!

Re: Using a Measure to create a new column that can be used to filter

tackytechtom — Thu, 23 Jun 2022 21:46:57 GMT

Hi Bernstra ,

How about this:

TypeSwitchMeasure = 
SWITCH (
    TRUE(),
    MAX ( TableFilterExample[Type] ) = "T1", "TA",
    MAX ( TableFilterExample[Type] ) = "T2", "TB",
    MAX ( TableFilterExample[Type] ) = "T3", "TB",
    BLANK()
)

Note, measure cannot be used in slicers, so you need to use the fiilter pane to do filtering. Also, I highly recommend to ask your back end people to add the new types to the analysis services model.

Hope it helps you anyway! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Using a Measure to create a new column that can be used to filter

Bernstra — Mon, 27 Jun 2022 14:14:42 GMT

Thank you tackytechtom. Unfortunately, I think getting the new types added to the model is probably the only way for this to work as we need.