Dynamic Table Creation???

[MJG2112]

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.  

[danextian]

  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.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[MJG2112]

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?

[Ashish_Mathur · Accepted solution]

You are welcome.

[MJG2112]

Thank you @Ashish_Mathur .  That's exactly what I need 😀

[Ashish_Mathur]

You are welcome.

[nielsvdc]

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.

[lbendlin]

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.

[WishAskedSooner]

@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.