Forum Discussion
How to retrieve context filter values
Hi there,
I am having a small obstacle using the context.
This is what I want
* Get the context filter values
This my table
| column_name_1 | column_name_2 |
| uuid-1 | 02-01-2023 |
| uuid-2 | 02-01-2023 |
| uuid-1 | 01-04-2023 |
| uuid-3 | 01-03-2023 |
| uuid-2 | 01-05-2023 |
| uuid-1 | 01-06-2023 |
* column_name_1 data are from db
* column_name_2 is a calculated column, written in DAX
* I am using an embedded report and I assign a filter via URL, It's like 'xxxxfilter=column_name_1 in ("uuid-1", "uuid-2")'
I want to have a column, that counts the number of distinct values of column_name_1 based on the filter, and I want to use this value in column_name_3. To conclude, I want something like as follow
if the filter is
'xxxxfilter=column_name_1 in ("uuid-1", "uuid-3")'
| column_name_1 | column_name_2 | column_name_3 |
| uuid-1 | 02-01-2023 | 3 |
| uuid-2 | 02-01-2023 | 0 |
| uuid-1 | 01-04-2023 | 3 |
| uuid-3 | 01-03-2023 | 1 |
| uuid-2 | 01-05-2023 | 0 |
| uuid-1 | 01-06-2023 | 3 |
As you can see, the column_name_3 are the number of occurrences of the value of column_name_1 based on the filter.
I am struggling to construct the column_name_3. It would be an easy one using SQL or Python, but I don't know how to do it when it comes to DAX and especially to make this value to be usable in another calculated column. (btw, I tried values, earlier to jump out of row context, but still failing to get the result like above).
Thanks in advance!
1 Reply
Hey bearideal ,
not sure about the requirement "I want to have a column ..."
You can not create a column inside a table that represents the filter value(s). The reason for this:
- a column is created during design time of the data model
- a calculated column (based on DAX) will only be re-calculated during data refresh, a data refresh is not initiated when a report is opened with a filter in the context
You have to create a measure.
I assume this will work:
measurename = countrows( allselected( tablename[columnname] ) )Hopefully, this provides what you are looking for.
Regards,
Tom
