Forum Discussion

asjones's avatar
asjones
Helper V
3 years ago

DAX Table - Filter Based on Selected Value

I am tryign to create a dynamci table in DAX based on another table called 'Award Mater File'.  However I want  to create a new virtual/DAX table that is filtered based on the selected value from a slicer.

I thought I had worked though my DAX errors, however now I just get a blank table in my on screen table visual.
My DAX for the table is:
Table 3 = CALCULATETABLE( 'Award Master File'  ,  'Award Master File' , FILTER( 'Award Master File' ,right( 'Award Master File'[Award] , 4) = right([Main Project # Selected] , 4)) )


In this table it references a measure called [Main Project # Selected] and tha is this DAX
Main Project # Selected = SELECTEDVALUE( 'Main Project Master File - PMF'[Main Project])

I had PLACEHOLDER errors and in reading I saw examples of using the FILTER value as normally the filter in the CACLULATETABLE function should compare against a hard value and not another measure.

At this point I get a blank table.

What am I doing wrong?

any and all suggestions welcome.

Alan
 
 
Need Help

2 Replies

  • igrandey89's avatar
    igrandey89
    Advocate II
    3 years ago

    It seems like there might be a few issues with your DAX formula.

    In your CALCULATETABLE function, you are specifying the 'Award Master File' table twice. The first instance of the table should be replaced with the table or expression you want to filter by. In your case, it should be replaced with the table or expression that contains the field [Main Project # Selected].

    You are also using the SELECTEDVALUE function to get the value of [Main Project # Selected]. This function only works when a single value is selected in the slicer. If multiple values are selected, the function will return an error. You might want to consider using the VALUES function instead, which will return a table of unique values for the selected column.

    Here's an updated version of your formula which I hope will work!

     

    Table 3 =
    CALCULATETABLE (
    'Award Master File',
    FILTER (
    'Award Master File',
    RIGHT ( 'Award Master File'[Award], 4 ) = RIGHT ( VALUES ( 'Main Project Master File - PMF'[Main Project] ), 4 )
    )
    )

     

    I've used the VALUES function instead of SELECTEDVALUE, and I've replaced the first instance of 'Award Master File' with the VALUES function. I've also removed the second instance of 'Award Master File' since it's not needed.

    I hope this helps!

  • asjones's avatar
    asjones
    Helper V
    3 years ago

    igrandey89 

     

    Thanks for the response, I really appreciate it. I am not sure how I ended up with the 2nd table name.
    I was using the selected value as I was trying to limit this table along with several other visuals with a slicer select 1. If multiple are selected here it is not critical as long as the visual is not to ugly with an error.

    When i use your code I a system error

     

    I tried to correct my table and it did not work either.

    Table 3 =
    CALCULATETABLE (
        'Award Master File',
        FILTER (
            'Award Master File',
            RIGHT ( 'Award Master File'[Award], 4 ) = RIGHT ( [Main Project # Selected], 4 )
        )
    )

     

    Any additional thoughts or guidance?

     

    thanks

     

    Alan