Forum Discussion
Dynamic Table Creation???
I've created some very simplfied mock data that hopefully explains what I'm trying to achieve for a user. Tables A & B are imported. Table C is a summarised from Table A, with columns added from Table B. As you can see, Table A is at employee level and Table B is at team level. Table C is also at team level. Visual 1 is created using Table C. So far so good. However, my user also wants to see Visual 1 with the OT from Table A excluded. How can I do this without creating a Table D and Visual 2? Is it possible to keep dynamcially refreshing Table C based on a user choice of include/exclude OT? I tried using a measure in the SUMMARIZE(FILTER...) that was based on button slicer but it resulted in an empty Table C.
You are welcome.
9 Replies
Is it possible to keep dynamcially refreshing Table C based on a user choice of include/exclude OT?No. Calculated Tables are computed once, and are not affected by user interaction.
You can have table variables inside a measure but the final result of that measure needs to be a scalar value.
lbendlin, This needs to be said more often. I have heard many mealy-mouthed explanations of this, but yours is the clearest and most factual yet.
Hi MJG2112, you could solve this by only creating a relationship between Table A and Table C. Table B would not have to be related to any other table. Then create the measures below and you are able to combine all of these in a single or multiple tables.
Total hours = CALCULATE( SUM(TableA[Hours]) ) Total ADJ = CALCULATE( SUM(TableC[ADJ]) ) With OT = CALCULATE( [Total hours] - [Total ADJ] ) Without OT = CALCULATE( [Total hours] - [Total ADJ], TableA[REG/OT] <> "OT" )Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
Hi,
PBI file attached.
Hope this helps.
Thanks Ashish_Mathur This is great. The only tweak I need is for the hours values in the visual to be either REG+OT or just REG - not REG or OT. Can you help please?
You are welcome.
I tried using a measure in the SUMMARIZE(FILTER...) that was based on button slicer but it resulted in an empty Table C.You probably used SELECTEDVALUE ('table'[filter column]). In the context of a calculated table, there isn't a single value selected as calc tables are not aware of any slicer selection thus the filter value is blank resulting to a blank table.
