Forum Discussion

jothi_prakash_a's avatar
jothi_prakash_a
Frequent Visitor
4 years ago
Solved

How to dynamically change columns in a Matrix or Chart

 

I have three components as mentioned above.

  1. Base data with two independent columns and one dependent column.
  2. A matrix to show the sum and count calculations.
  3. A slicer containing the column names of base data.

 

The question is how to dynamically change columns in matrix results when the slicer is changed from Column1 to Column2?

 

Note: I can't pivot the table as I have other calculations also running on top of the base data. And I have 70+ columns and 5M rows of data. So pivoting or creating a union-like table would make a 350M+ rows table which I don't need.

 

Thank you.

11 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Actually there is a way to accomplish this without the new fields parameter option. Bascially you need to build a table to use as the slicer listing the values of all the columns you need in a single column. Take this data as an example:

     

    To be able to filter the item, colour and type columns, we need to create a table to use as a slicer listing all the values & the column name (& and order column for sorting purposes.

    To create the new table, you can append the columns in Power Query or use the following DAX:

     

    Select Column Table =
    VAR _item =
        SELECTCOLUMNS (
            FactTable,
            "SelColumn", FactTable[Item],
            "ColumnName", "Item",
            "Order", 1
        )
    VAR _colour =
        SELECTCOLUMNS (
            FactTable,
            "SelColumn", FactTable[Colour],
            "ColumnName", "Colour",
            "Order", 2
        )
    VAR _type =
        SELECTCOLUMNS (
            FactTable,
            "SelColumn", FactTable[Type],
            "ColumnName", "Type",
            "Order", 3
        )
    RETURN
        DISTINCT ( UNION ( _item, _colour, _type ) )
    

     

    to get..

     

    Next create relationships between the SelColumn field in this new table and each of the columns in the fact table:

     

    Then a simple SUM measure and the following measure to use in the matrix:

     

    Select Column Measure =
    SWITCH (
        SELECTEDVALUE ( 'Select Column Table'[ColumnName] ),
        "Item", [Sum Value],
        "Colour",
            CALCULATE (
                [Sum Value],
                USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Colour] )
            ),
        "Type",
            CALCULATE (
                [Sum Value],
                USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Type] )
            )
    )
    

     

    or if you also need the grand total (which I don´t think makes much sense since the result is multiplied by the number of columns selected):

     

    With totals =
    SUMX (
        'Select Column Table',
        CALCULATE (
            SWITCH (
                SELECTEDVALUE ( 'Select Column Table'[ColumnName] ),
                "Item", [Sum Value],
                "Colour",
                    CALCULATE (
                        [Sum Value],
                        USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Colour] )
                    ),
                "Type",
                    CALCULATE (
                        [Sum Value],
                        USERELATIONSHIP ( 'Select Column Table'[SelColumn], FactTable[Type] )
                    )
            )
        )
    )
    

     

    To get:

     I've attached the sample PBIX file

    • jothi_prakash_a's avatar
      jothi_prakash_a
      Frequent Visitor

      This is a viable solution. In my case, the data would be too long. 428M rows of data to be precise. That increases the size of the PBIX file to nearly 1.5GB which causes trouble while hosting. As there's a 1Gb upload limit.

    • jothi_prakash_a's avatar
      jothi_prakash_a
      Frequent Visitor

      This is a great solution for the May 2022 version, SolomonovAnton

       

      I'm using May 2021 because the Report server is compatible with it. So if there's a solution to May 2021 Please share, I'll accept this as the solution too.

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion

        Create a new unrelated table (use the Enter Data option in the ribbon an type in the names of the measures you wish to toggle between). Use this table as the slicer.

        next create the following  equivalent measure to use in the matrix:

        toggle = IF(SELECTEDVALUE(SlicerTable[Measure]) = "Measure 1", [Measure 1], [Measure 2])

  • mahenkj2's avatar
    mahenkj2
    Icon for Solution Sage rankSolution Sage

    Hi jothi_prakash_a ,

     

    If this report is published, you can give users the right to personalize as needed.

    In that case, they can select whatever column they need and it will be right away done.

     

    Hope it helps.