Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Dynamic table based on slicer selection

  • 1.  Dynamic table based on slicer selection

    Silver Contributor
    Posted Feb 03, 2021 05:54 AM
    Hi All,
    I have a matrix where there are several columns available in the table and i have a slicer as well. In the slicer i have 4 options let say, IO, IC, Capacity and SD. Is it possible if i select IO or SD then show me full matrix table but when i select IC or Capacity show me limited number of columns. So if i select IC or capacity from the slicer i should see only till Client satisfaction , rest should hide.


    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------


  • 2.  RE: Dynamic table based on slicer selection

    Posted Feb 03, 2021 07:58 AM
    I don't know of a way to do it exactly as you describe, but you could create a bookmark and toggle and have two matrices, one on top of the other, that are hidden/revealed when the toggle is switched.  Check out: https://www.youtube.com/watch?v=_Afcj8mT5_Q

    ------------------------------
    Matthew Shuster
    Director of Performance Technology
    ------------------------------



  • 3.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted Feb 03, 2021 11:55 AM
    Hi Mathew,
    Thanks for the reply but this method i know and i dont want to use button. I just want to use slicer and change the table.

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------



  • 4.  RE: Dynamic table based on slicer selection

    Top Contributor
    Posted 28 days ago
    Hi @Krishna Nandan,

    Please look into calculation groups. You can define multiple measures in a single calculation group and depending on the slicer selection you can have those calculations displayed.

    You will need to tinker around with it, but I think it is a feasible solution.

    Thank you,​​

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 5.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted 27 days ago
    Hi Vishesh
    thank you for your reply. The solution you have given, i tried that method by DAX selectedvalue but the problem is, it hide values but the column title will remain same it would not change bases on the selection and i can apply conditional color on column titles

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------



  • 6.  RE: Dynamic table based on slicer selection

    Top Contributor
    Posted 27 days ago
    Edited by Vishesh Jain 27 days ago
    Hi @Krishna Nandan,

    Disjointed tables and the SELECTEDVALUE() function can also get the job done but as you pointed out, the column names are not dynamic.

    So try using Calculation Groups instead. For that you will have to define Calc groups for every value you have in your slicer.
    This way the column title (calculation/measure names) can be controlled.

    As far as conditional formatting is concerned, you can write some DAX and change the colors of the values and the titles.
    I speak from experience as I have done something like this before and yes it is a tedious job but I think it will get the job done.

    Thank you,

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 7.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted 24 days ago
    Hi Vishesh,
    I have never used Calculation group, so i dont know how to use it. Conditional formatting will not work for column title i guess.

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------



  • 8.  RE: Dynamic table based on slicer selection

    Top Contributor
    Posted 19 days ago
    Hi @Krishna Nandan,

    Please have a look at the following video on how to create calculation groups using tabular editor.

    https://www.youtube.com/watch?v=a4zYT-N-zsU

    Alternatively, you can even use disjointed tables.

    If you could upload a sample dataset along with the desired result, I can take a dig at it.

    Thank you,​

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 9.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted 19 days ago
    Hi Vishesh,
    I am attaching my sample set along with Power BI. I have one option that i can use a card for metric part, whenever someone select IC or IC capacity it will hide rest of the data and for other selection it will be normal.
    However, just try if you better solution, 


    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------

    Attachment(s)

    xlsx
    sample data.xlsx   10 KB 1 version


  • 10.  RE: Dynamic table based on slicer selection

    Top Contributor
    Posted 19 days ago
    Hi @Krishna Nandan,

    I tried using calculation groups and it didn't work, as it doesn't work well with columns.

    Apologies!

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 11.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted 19 days ago
    Hi Vishesh,
    No issue, i got the desired result by using different method. Thank you for your help

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------



  • 12.  RE: Dynamic table based on slicer selection

    Top Contributor
    Posted 13 days ago
    Please can you share your solution with the community, so that others with the same problem can reach the solution faster.

    Thank you,

    ------------------------------
    Vishesh Jain
    MCSA Power BI and Excel
    Emerald Award Dynamic Communities 2019
    Owner
    VR Construction
    ------------------------------



  • 13.  RE: Dynamic table based on slicer selection

    Silver Contributor
    Posted 13 days ago
    Hi Vishesh,
    Nothing Special i have done. I just use a card and kept it over the half of the table, then i used the DAX to hide (transparent basically) based on the slicer selection.
    Card hide = IF(SELECTEDVALUE('HeatMap Data'[IO/IC]) = "IC" ,"#CCCCCC", IF( SELECTEDVALUE('HeatMap Data'[IO/IC]) = "IC Capacity","#CCCCCC","#FFFFFF00" ))
    Heatmap data is my table and IC or IC capacity is my slicer selection. Here one thing is important that, we can not keep 100% transparent in visuals by using format so i put 00 after #FFFFFF hex code, it will give me 100% for FFFFFF and for CCCCC i need 0% transparency so i kept as it is.

    thanks
    Krishna ​

    ------------------------------
    Krishna Nandan
    SME
    Bangalore
    ------------------------------