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

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.

  • Shawnee's avatar
    Shawnee
    Frequent Visitor
    5 years ago

    Thank you! Thye solution worked 🙂