Forum Discussion

rlmeyer's avatar
rlmeyer
Icon for Resolver I rankResolver I
5 years ago
Solved

Matrix Column Name Based on Slicer Selection

Hello,

 

I have a matrix that shows the project's name in the column section.  I would like to be able to switch between the real name and a hidden name from a slicer. I have two columns in my table, one with the real name and one with a random name. I was able to create the table for the slicer and the measure that will change the name in the values section of the matrix table, but I cannot put that measure in the column section. It appears that only calculated columns can be used in the matrix column section, but I have not found a way to apply a slicer to a calculated column. Any thoughts on how best to accomplish this?

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi rlmeyer 

    I think you want to build a dynamic column header in Matrix visual. However we can't add a measure in column, you may transform your data model as below and build a measure to achieve your goal.

    Due to I don't know your data model, I build a sample.

    Fact Table:

    Then build a Hidden Table and Append two tables as a new one in Power Query Editor.

    New Table:

    Build a Slicer table and build a Measure.

     

    Measure =
VAR _select =
    SELECTEDVALUE ( Slicer[Name] )
RETURN
    IF (
        _select = "Estimate Name",
        CALCULATE (
            MAX ( Append1[Value] ),
            FILTER ( Append1, Append1[Column] <> "Hidden Name" )
        ),
        CALCULATE (
            MAX ( Append1[Value] ),
            FILTER ( Append1, Append1[Column] <> "Estimate Name" )
        )
    )

     

    Result is as below.
    Default:
     
    Select Estimate Name:
    You can download the pbix file from this link: Matrix Column Name Based on Slicer Selection
     
     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    rlmeyer 

     

    If what you wish to achieve is a "one or other" column, you can use a measure along the lines of :

    Switching columns =
    IF(SELECTEDVALUE(Your column slicer[column]) = "Estimate name", [measure for Estimate name values], [measure for hidden name values]).

    (Just make sure you name both measures as you wish to see them in the column header)

    You can then create the matrix by adding the whatever you need as rows, and this [Switching columns] measure in the "Values" bucket. (leave the "Columns" bucket empty)

    If, however, you need additional columns in the matrix, you need to create a "custom" layout.  

    Check this blog post on how to do this:

    Creating a “Custom” or “Hybrid” matrix 

     

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago
    You are correct that a calculated column's values will not change based on slicer selection. This is because of order of operations in Power BI and I've had 3 similar questions today, so wrote a short summary here: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

    What you can do though is hide/show columns based on slicer selection. What is more specifically your requirements? How much data do you have?

    You could try creating a table using DAX that uses SUMMARIZECOLUMNS() or similar to create a table by Structure Type that has the following columns;
    Structure Type, Hidden Status, Project Name, Value
    The Hidden Status column will have 'Estimate Name' and 'Hidden Name' for its values. Project Name column would have the hidden and estimate names, and the values column would be the same for both hidden and estimate.
    Then put Hidden Status in your slicer, Project Name in Columns in the matrix, Structure Type in rows.

    If you want more help with this, I'll need to know more details on what your data actually looks like, measure calculations and desired output (am I right that the value doesn't change, just the column name?)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rlmeyer 

    I think you want to build a dynamic column header in Matrix visual. However we can't add a measure in column, you may transform your data model as below and build a measure to achieve your goal.

    Due to I don't know your data model, I build a sample.

    Fact Table:

    Then build a Hidden Table and Append two tables as a new one in Power Query Editor.

    New Table:

    Build a Slicer table and build a Measure.

     

    Measure =
VAR _select =
    SELECTEDVALUE ( Slicer[Name] )
RETURN
    IF (
        _select = "Estimate Name",
        CALCULATE (
            MAX ( Append1[Value] ),
            FILTER ( Append1, Append1[Column] <> "Hidden Name" )
        ),
        CALCULATE (
            MAX ( Append1[Value] ),
            FILTER ( Append1, Append1[Column] <> "Estimate Name" )
        )
    )

     

    Result is as below.
    Default:
     
    Select Estimate Name:
    You can download the pbix file from this link: Matrix Column Name Based on Slicer Selection
     
     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.