Switch not working (only passing alternate value)

[David_Ramos]

Scenario:-
• User selecteable Reporting level on slicer (running from static reference table)
• measure for the hierarchy Level works OK

   RLSLevel = SELCTEDVALUE(RefEntryLevel[EntryLevel],3)

   This displayes on a card OK

   Test Column in structure table works OK when displayed on a table visualisation

• Dynamic structure DOES NOT change as expected

   - using SWITCH(RLSLevel,1,[ColA],2,[ColB],2,[ColC])

   * in the same table where I'm testing the RLSLevel output - another column will not update - ONLY bringing in the value of the alternate (which can be seen if I change the alternate in the switch between 1,2 or 3) thus ignoring the first part of the switch calculation

   * Attempting to use an IF statement instead of switch also doesn't immediately update the table column

   - IF(RLSLevel=1,[ColA],IF(RLSLevel=2,[ColB],[ColC]))

to me - it appears that no matter the result of the SELECTEDVALUE in the first part of the statement (as I see it working in the table result) - when the combining the result directly in a calculated column - it fails to bring in the expected value - ONLY carrying through the alternate - so, at best it delivers inconsistent results back to data and at worst it's completely unusable.

I see from other posts this has been a bit of an issue in various forms for some time (back to 2017) .. any update on a resolution for this?

[MFelix]

Hi  @David_Ramos ,

Not sure if I understood correctly what you are trying to achieve, can you confirm to me that you are creating a new column on your model?

If that is the case the use of measures does not work on columns in the dataset because of the context, columns have row context and measure don't have it.

Can you share some sample data please?

[David_Ramos]

yes - I'm creating a new column (well .. sets of new columns) .. I know there are much better ways of dealing with hierarchies - but for this model I'm stuck with some ridiculous requirements of the business both at data provision and expected output). There are 3 sets 5 of columns coming in, and dependant on the 'selected view' I need to shift which set is used for filters and data in a static table for output.

in theory - what I want to achieve should be simple:-

Coumn 1	Column 2	Column 3	Dynamic Column
David	Ramos	David Ramos

I was hoping to use the RLS (UserPrincipalName) alongside a marker to drive a measure but I run into issues with the use of UPN when I got to the dynamic column. So I went down a 'selector' method - either by way of static table to return a number (for switch) on a selectedvalue against a slicer or a numeric parameter.

Reading your explanation - I guess the core question is - if a measure can't be used in the context of a column (or selectedvalue as a VAR in the calculated column) - would you know of a way to create a dynamic column here?

[MFelix]

Sorry for the additional questions but can you explain a little bit better what you need to achieve?

You want to limit the data that the users sees or do you want to have it dynamic?

Can you make some example please?

[David_Ramos]

I'm wanting the 'dynamic column' to be dynamic - but it needs to be within the dataset - so that single item is useable throughout the rest of the report for that user (based on RLS).

Unfortunately - our IT setting prevent me uploading any data (veen non business related), so I put a small example in previous post.

essentially - I need to create a mechanism that either

• the RLS model determines the variant structure (type 1-3) or

• Operator selects this from a slicer to determine type 1, 2 or 3)

then with the result of that:-

.Populates type 1, 2 or 3 into the 'Dynamic column' ready to be used by other slicers and also for a fixed item on a report table for download.

So ... from the Data

Column 1	Column 2	Column 3
This Thing	That thing	Another Thing
Something	Everything	What Thing

Option 1 would return

Column 1	Column 2	Column 3	Dynamic Col
This Thing	That Thing	Another Thing	This Thing
Something	everything	What Thing	Something

Option 2 would return

Column 1	Column 2	Column 3	Dynamic Col
This Thing	That Thing	Another Thing	That Thing
Something	Everything	What Thing	Everything

Option 3 would return

Column 1	Column 2	Column 3	Dynamic Col
This Thing	That Thing	Another Thing	Another Thing
Something	everything	What Thing	What Thing

the key is that the data in the Dynamic Column would be a 'swap out' based on the option, rather than hiding the unused columns like a normal parameter action would result in

Hope that makes sense - and thanks so much for you help!