Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arsene49
Helper I
Helper I

DAX - swapping the columns when a slicer values are created

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

below is the sample table -

arsene49_0-1618928061197.png

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.

arsene49_0-1618976077850.png

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

arsene49_1-1618976097018.png

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

 

Thanks

7 REPLIES 7
VijayP
Super User
Super User

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


@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. 

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:

image.pngimage.png 

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.

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?

Hi @arsene49 ,

 

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

 

image.pngimage.png

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.

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.