Dynamic column in matrix visual

[Anonymous]

Hey all,

I need to have dynamic columns in matrix visual. i have few columns in my tables(different tables) . i want these columns as options in slicer. when i select particular slicer that column should get added to matrix visual and when i unclick it, it should be removed.

I tried with unpivoting, its not working. For reference, iam posting screenshot of requirment here.

Please let me know solution for this asap.

[Mariusz]

Hi @Anonymous 

I probably would suggest using either Paginated Reports or Combination of Bookmarks and Buttons to show/hide the columns in the matrix,  

I head a go at it using DAX and the result is on the attached. 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

@Mariusz what you mean by paginated reports?

[Mariusz]

Hi @Anonymous 

Please see the link, essentially its bringing SSRS reports to Power BI, however premium capacity is needed for that.

https://docs.microsoft.com/en-us/power-bi/paginated-reports-report-builder-power-bi

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

@Mariusz Thanks so much . I had gone through your pbix. Can you please what your dax is doing over there.i mean its main functionality?

[Mariusz]

Hi @Anonymous 

Sure, please see the explanation commented in the expression.

Measure = 
VAR __sum = SUM( 'Table'[Values] ) -- sum of values 
VAR __selction = VALUES( 'Columns'[Column1] ) -- list of values selected on the slicer 
RETURN 
SWITCH(
    TRUE(),
    /* 
        ISINSCOPE - 
            Returns TRUE if the column is in included in the filter context -
            t will return TRUE for the column if it is used in the Matrix and all columns with lower granularity 
            ISINSCOPE( 'Table'[Level3] ) will return true for level3 and level4 
        NOT 
            Reverses TRUE to FALSE, so when ISINSCOPE( 'Table'[Level3] ), TRUE will be set on 'Table'[Level1] and 'Table'[Level2]
        "Level2" IN __selction 
            Is the second part of SWITCH logical test that has to be met 
            "Level2" is a text that is searched for in currently election of Columns'[Column1] / slicer

        && 
            is other way of saing AND in DAX 
            Both cryteria, NOT ISINSCOPE( 'Table'[Level3] ) and  "Level2" IN __selction -- have to be met for __sum value to be returned
    */
    NOT ISINSCOPE( 'Table'[Level2] ) && "Level1" IN __selction, __sum, 
    NOT ISINSCOPE( 'Table'[Level3] ) && "Level2" IN __selction, __sum,
    NOT ISINSCOPE( 'Table'[Level4] ) && "Level3" IN __selction, __sum,
    "Level4" IN __selction, __sum
)

 let me know if it's clear enough for you.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Thanks so much, but if you can see here, pbix attached will occupy space for all columns even though slicer selection is blank or single, whereas requirment doesnt have it.

How about creating custom visual, where users should have ability to add column dynamically to matrix. Is it possible?

[Anonymous]

@Mariusz  as per your selection, when i select level 2 , both level1 and level2 are adding to visual. can we do like-only individual column should get added. I mean, when i select level3 only level3 column should get added.

[Anonymous]

@Mariusz iam trying to do the way you did in sample pbix, but dynamic nature is not working here.. Do i need to have updated version of powerbi to work with this approach

[V-lianl-msft]

Hi @Anonymous ,

Based on your description,If there is nothing selected in the slicer, you don’t want to see the value of the matrix.

Here's a blog that can solve your current problems:https://exceleratorbi.com.au/show-or-hide-a-power-bi-visual-based-on-selection/

Best Regards,

Liang

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