PBI Community,
I have ran into a little hiccup with a report I have put together and am looking for some support. I have written a table expression and am using this custom table to essentially replace my upper most parent tables in my data model. I have incorporated a series of filter expression in the table expression (see screenshot #1) that contains static values that allows me to reduce the overall context (number of rows). I am now attempting to improve the functionality of the report by changing these static values to dynamic however I am not getting the response I was looking for. I have create a few supporting tables (see screenshot #2, button #2) and have referenced these support tables in several ways, as filter arguments, and the table context just seems to reset to a larger number of rows; see screenshots #3 and #4 as reference.
I have used these technique before but within measures (not new DAX tables) so I am wondering whether I am seeing a limitation when try to embed variables/measures inside the table expression. Does anyone see anything wrong here that I am missing?
Thanks in advance for the support!
Screenshot #1
Screenshot #2
Screenshot #3
Screenshot #4
So just as a follow up, I was troubleshooting this problem and it looks like no matter which method I try, the value returns zero as the filter argument which explains why the filter context resets.
There’s a bit going on with this one but I think I may understand a little bit about it.
Overall your model does look a little complex. Have you looked at ways of simplifying this?
To me the issue you’re experiencing is that you are trying to embed dynamic variables from other supporting tables into a table function. Unfortunately this just isn’t possible. Table functions are slightly different to DAX measures and then don’t pick up on selections that are made say on a report page.
The reason is because tables created with DAX only update on refresh, not in real time like measures do. Also with variables, the SELECTEDVALUE function will unlikely be picking up any results, that why you’re not ultimately getting any.
I would really break things down here. Try seeing what’s not working one by one, that way you’ll be able to see what’s breaking down in your formulas. This is important when you have ones like you have here.
One other tip I thought I would mention, is that to me the strategy of create multiple tables like this seems like a lot of additional work. Can some or all of this be moved back into the query editor. Maybe you could do some merging and cleaning up of your table there.
Chrs
Sam
Sam,
Thanks for the response! I know you are a busy man and appreciate the time you dedicate to this community.
Regarding your comments about the data model, please see below screenshot #1 as a reference. The data model is a relationship database that I use within my organization and that was constructed by a 3rd party. Every table has a parent/child identifier and some tables have unique keys that allows me to related from child/child tables (which explains the inactive relationships). I started building this report off of an existing file and so the data model contained a few tables that I am not using right now. I have deleted the non-essential tables and am highlighting the following in screenshot #1: button 1 = Oracle (and Azure SQL) database queries, button #2 = custom DAX tables and button #3 = static supporting tables. In my opinion, this data model is as simple as it gets unless i start merging queries together within Power query however I will begin duplicating dimensional data.
Regarding your comments around the difference between a DAX measure and DAX table, also in the same report I have created another DAX table that is using logic you taught me with your online courses. I am noticing that when I manually change the filter arguments in the DAX table referenced in my first post, this supporting DAX table “time/cost outlier” is updated (see screenshot #2 as reference). In my mind, i should be able to achieve the same thing by embedding a measure as a filter argument and have this toggled when manipulating the selection from the static supporting table (as shown in my original post expression #3).
I am willing to accept that this is a limitation with table functions, specifically embedding variables/measures as filter arguments inside a table function, and so I will continue on with the report without the extra functionality. I thought i would reach out to the sensei for advice and I appreciate the time you set aside for your support.
Screenshot #1
Screenshot #2
Ok that’s great. Sounds like your on top of everything.
Chrs
Sam
