I have an issue I am working on and have tried a couple different methods but wondering if there is a better way.
I have two tables, REVENUE and WAGES.
These tables are linked together with a key made up of a Route ID, year, and month.
There is a field in the WAGES table called "EN CODE" that can differ across the records for that particular key. So if it were key "ABCDEF|2019|8", there could be 30 records with 5 distinct values in the EN CODE column. There is also a field in that table called "Date Worked".
I am trying to create a field called "EN Code(s)" that lists all the distinct values concatenated together from the EN CODE field for the current filtered data. It needs to be dynamic depending on what the filters are that the user has chosen.
I have tried creating the field in SQL but of course then it is not dynamic. It lists everything for that key regardless of whether you add additional filters. I have also tried creating a separate query in the Query Editor, using Group By and Extract Values, but then the other fields that I would want to filter off of are no longer available in the table (Date Worked).
Your test seems to work well with folders. Here is my pbix with a simple data table, file If what you are trying to do is to create a column in a table based on the users' filters, I have never seen that. However if you are doing it for a dashboard this seems to do the job. Let us know if there is more intricacies that need to be dealt with.
Did I answer your question? Mark my post as a solution!