Forum Discussion

Shawnee's avatar
Shawnee
Frequent Visitor
5 years ago
Solved

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:

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.

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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's avatar
      Shawnee
      Frequent Visitor
      5 years ago

      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's avatar
        MFelix
        Super User
        5 years ago

        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.