Forum Discussion

MJG2112's avatar
MJG2112
Icon for Advocate II rankAdvocate II
10 months ago
Solved

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.  

Need Help
Tips and Tricks

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    10 months ago
    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's avatar
      WishAskedSooner
      Icon for Continued Contributor rankContinued Contributor
      10 months ago

      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.

  • nielsvdc's avatar
    nielsvdc
    Icon for Super User rankSuper User
    10 months ago

    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.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    10 months ago
      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.