Forum Discussion

AndyTrezise's avatar
AndyTrezise
Advocate IV
8 years ago
Solved

Create Table using a measure

I'm trying to create a table based on another table by filtering using a measure.   Table2 = VAR SeletedYear = IF ( COUNTROWS( ALLSELECTED( TimeSet[Year] ) ) = 1, VALUES(TimeSet[Year]), BLANK())...
Need Help
  • eniX's avatar
    eniX
    8 years ago

    You define calculated tables with DAX but they are not dynamic. Basic workaround could look like this:

    • Reference your original table twice
    • Create calculated column
      • For the second table with year - 1
      • For the third table with year - 2
    • Create look-up table for years

     

    Now you can create slicer out of your look-up table and slice all three tables:

AndyTrezise's avatar
AndyTrezise
Advocate IV
8 years ago

Thanks for the response

 

What I failed to put in the previous post is that I also need a third table - so I would end up with Table 1, Table 2 and Table 3.

 

Its not not practicle to get the user to filter each table seperately. That's 3 x slicers when I only need them to pick from one so I need to do this interactively through DAX - I think :-)

 

I just want to be able to select a year (or year & month) from a single slicer and then filter table 1 to that selection, table 2 to year+1 and table 3 to year+2.

 

I'm not quite sure why in my previous example if I set the variabe to a static number it works but if I get the number from the selected value in the slicer it doesn't.

 

Is there any other way of filtering 3 tables via one slicer through code/DAX?

eniX's avatar
eniX
Helper III
8 years ago

You define calculated tables with DAX but they are not dynamic. Basic workaround could look like this:

  • Reference your original table twice
  • Create calculated column
    • For the second table with year - 1
    • For the third table with year - 2
  • Create look-up table for years

 

Now you can create slicer out of your look-up table and slice all three tables: