Alias values using selection from same table

[Shawnee]

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:

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

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.

[MFelix]

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:

Check PBIX file attach.

[Shawnee]

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

[MFelix · 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:

Check the connection of the PBIX file.

[Shawnee]

Thank you! Thye solution worked 🙂