Dynamic column in matrix visual

Question

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.&nbsp;I tried with unpivoting, its not working. For reference, iam posting screenshot of requirment here.Please let me know solution for this asap.

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
I probably would suggest using either Paginated Reports or Combination of Bookmarks and Buttons to show/hide the columns in the matrix,&nbsp;&nbsp;
&nbsp;
I head a go at it using DAX and the result is on the attached.&nbsp;
&nbsp;
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

anonymous · Answer

Mariusz&nbsp;what you mean by paginated reports?

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
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
&nbsp;
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.
&nbsp;
&nbsp;

anonymous · Answer

Mariusz&nbsp;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 · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
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

&amp;&amp; 
            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] ) &amp;&amp; "Level1" IN __selction, __sum, 
    NOT ISINSCOPE( 'Table'[Level3] ) &amp;&amp; "Level2" IN __selction, __sum,
    NOT ISINSCOPE( 'Table'[Level4] ) &amp;&amp; "Level3" IN __selction, __sum,
    "Level4" IN __selction, __sum
)
&nbsp;let me know if it's clear enough for you.
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.
&nbsp;

Forum Discussion

Dynamic column in matrix visual

9 Replies

Related content

Dynamic Column Header In a Matrix

Compare Dynamic Columns in a Matrix

Dynamic column header in Matrix

Dynamic Column Difference in Matrix

Matrix Column Widths