DAX - swapping the columns when a slicer values are created

[arsene49]

Hi All,
Need DAX help in swapping the columns when a slicer values are created.

below is the sample table -

i need a slicer with values "NEW" and "OLD". and need to calculate a calculated column called "SA Calculated".

when NEW is selected, SA Calculated should show SA New. and below is the expected output.

when OLD is selected, SA Calculated should show SA Old. and below is the expected output.

NOTE: SA Calculated column also to be used as filter in this report.

Thanks

[VijayP]

@arsene49 

When you create any measure , it will produce numbers . I think you need to eloborate you Measure more detail.

However it is achievable, share the some sample data ( not in the above format) so that I can help you

The solution will be like this

Crate a dummy table with a single column containing old and new (A column say "Type")

Create a slicer with Type column
then using Create a measure 
VAR Selection = Selectedvalue(Type)
return
if ([Selection]="NEW", measure related to New, measure related to Old).

Let me know if any more information required

[arsene49]

@VijayP  thanks for the reply.

output should be a column not measure. and it should swap based on slicer value.

i need to use that new column as a filter also.

please check my expected output. 

[v-kkf-msft]

Hi @arsene49 ,

Try the following formula to create calculate table:

Calculate Table = 
UNION( 
    SELECTCOLUMNS(
        'Table',
        "SA Calculated", 'Table'[SA New],
        "Category", "New"
    ),
    SELECTCOLUMNS(
        'Table',
        "SA Calculated", 'Table'[SA Old],
        "Category", "Old"
    )
)

Then put the Category column into the slicer. The calculation table and filter results are as follows:

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arsene49]

thanks @v-kkf-msft for the reply.

I had this solution in my mind. there is a cost centre associated to each SA (check my sample data, first screen shot). If i union then cost centre will duplicate and this is a lookup table and i have to join this table to a fact table based on cost sentre column in my model and it results in many to many replationship with bidirectional filtering. how can i resolve this issue?

[v-kkf-msft]

Hi @arsene49 ,

Unpivot columns SA New and SA Old columns in the Power Query, and the results are shown below. Is this method appropriate?

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft]

Hi @arsene49 ,

I don't know why you refuse to use measures.

But if the calculated column is not necessary, I think this method Creating a “custom” or “hybrid” matrix in PowerBI  is helpful to you.

Best Regards,
Winniz

[arsene49]

@v-kkf-msft expected output should show all rows.

and whatever calculated column which we create needs to be used in filter also.