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

[Bernstra]

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!

[tackytechtom]

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/

[Bernstra]

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.