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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shawnee
Frequent Visitor

Alias values using selection from same table

Hi All,

 

I have been looking for a solution to this problem for quite some time now and have had no luck to date.

 

Basically what I want to achieve is the following:

 

When the slicer value is selected I want it to be displayed as the selection in a table and all other values displayed as an alias so you will only know who the selected value is but not the other values.

 

example:

Shawnee_0-1600328309522.png

The alias value for each is in the same values table so for instance the back end data looks like this:

Shawnee_1-1600328460080.png

So logically if slicer "Apples" is selected, all other fruits show their respective alias, so if selected show fruit else show Alias.

 

Hope this is clear, I am hoping there is a clever DAX formula someone could help me out with.

 

Thanking you in advance.

 

1 ACCEPTED SOLUTION

Hello @Shawnee ,

Create a discounted table with fruits and aliases:

SELECTED_AXIS =
UNION (
    ADDCOLUMNS ( DISTINCT ( Alias[Fruit] ); "Type"; "Fruit" );
    ADDCOLUMNS ( DISTINCT ( Alias[Alias] ); "Type"; "ALIAS" )
)

Add the following measure:

Total_Axis = 
IF (
    HASONEVALUE ( SELECTED_AXIS[Fruit] );
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( SELECTED_AXIS[Fruit] ) IN VALUES ( Alias[Fruit] )
            && SELECTEDVALUE ( SELECTED_AXIS[Type] ) = "Fruit";
            CALCULATE (
                SUM ( 'Values'[Value] );
                FILTER (
                    ALL ( Alias[Fruit] );
                    Alias[Fruit] = SELECTEDVALUE ( SELECTED_AXIS[Fruit] )
                )
            );
        NOT ( SELECTEDVALUE ( SELECTED_AXIS[Fruit] ) IN VALUES ( Alias[Alias] ) )
            && SELECTEDVALUE ( SELECTED_AXIS[Type] ) = "Alias";
            CALCULATE (
                SUM ( 'Values'[Value] );
                FILTER (
                    ALL ( Alias[Alias]; Alias[Fruit] );
                    Alias[Alias] = SELECTEDVALUE ( SELECTED_AXIS[Fruit] )
                )
            );
        BLANK ()
    );
    CALCULATE ( SUM ( 'Values'[Value] ); ALL ( 'Alias'[Fruit] ) )
)

Now create the table and chart using column SELECTED_AXIS and Total for values, note that if you use an array the total calculation may need some adjustments:

slicers_alias.png

Check the connection of the PBIX file.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @Shawnee ,

 

  • Add an Index on the aliases table.
  • Create a table with the fruits for the slicer selection
  • Add the following measure: 
Fruit Alias = 
IF (
    SELECTEDVALUE ( 'Values'[Fruit] ) in VALUES(  Slicer[Fruit] );
    MAX ( 'Values'[Fruit] );
    LOOKUPVALUE ( Alias[Alias]; Alias[Fruit]; SELECTEDVALUE ( 'Values'[Fruit] ) )
)

 

Now create the table visualition using the Index has your reference:

slicer_selection.gif

 

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

This solution does not quite work for what I need as there needs to be a direct relationship to the slicer value as it filters my entire report. Is there anyway to do this by creating a relationship between the slicer and the main table where this solution still works?

 

Also, I need to be able to use this is a matrix visual and I am not sure how this solution will work in a matrix as measures cant be used as rows.

 

Thank you for the solution though, it almost solved my problem.

 

KInd regards,

Shawnee

Hello @Shawnee ,

Create a discounted table with fruits and aliases:

SELECTED_AXIS =
UNION (
    ADDCOLUMNS ( DISTINCT ( Alias[Fruit] ); "Type"; "Fruit" );
    ADDCOLUMNS ( DISTINCT ( Alias[Alias] ); "Type"; "ALIAS" )
)

Add the following measure:

Total_Axis = 
IF (
    HASONEVALUE ( SELECTED_AXIS[Fruit] );
    SWITCH (
        TRUE ();
        SELECTEDVALUE ( SELECTED_AXIS[Fruit] ) IN VALUES ( Alias[Fruit] )
            && SELECTEDVALUE ( SELECTED_AXIS[Type] ) = "Fruit";
            CALCULATE (
                SUM ( 'Values'[Value] );
                FILTER (
                    ALL ( Alias[Fruit] );
                    Alias[Fruit] = SELECTEDVALUE ( SELECTED_AXIS[Fruit] )
                )
            );
        NOT ( SELECTEDVALUE ( SELECTED_AXIS[Fruit] ) IN VALUES ( Alias[Alias] ) )
            && SELECTEDVALUE ( SELECTED_AXIS[Type] ) = "Alias";
            CALCULATE (
                SUM ( 'Values'[Value] );
                FILTER (
                    ALL ( Alias[Alias]; Alias[Fruit] );
                    Alias[Alias] = SELECTEDVALUE ( SELECTED_AXIS[Fruit] )
                )
            );
        BLANK ()
    );
    CALCULATE ( SUM ( 'Values'[Value] ); ALL ( 'Alias'[Fruit] ) )
)

Now create the table and chart using column SELECTED_AXIS and Total for values, note that if you use an array the total calculation may need some adjustments:

slicers_alias.png

Check the connection of the PBIX file.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you! Thye solution worked 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors