Forum Discussion
Helper IIUse measure as a filter
Hi!
Edit: check this reply for a clearer example and a dummy pbix file: https://community.powerbi.com/t5/Desktop/Use-measure-as-a-filter/m-p/3046873/highlight/true#M1036124
I have a bar chart that shows the amount of users that have checked each answer of each question on several forms. For this, I have a table FORMS with the columns ID, ID_CENTRE and DATE, and another table FORMS_ANSWERS with the columns FORM_ID (relation *|1 with FORMS.ID), QUESTION, ANSWER and SN_MARCADA (that shows if the answer is checked).
I just want to show the answers of the latest forms of each center, what I call "the active forms".
Before, I had a calculated column on the table FORMS that had "TRUE" or "FALSE" depending of the date, and I had it on the filters section like this:
But then I had to make it vary with a date filter, so I created a measure to check which were the active forms depending of the date, like this:
3 Replies
- amitchandak
Super User
Fátima , Not very clear. But make that measure as visual level filter
FátimaHi amitchandak !
I'm sorry, it's a bit tricky to explain. I attach a wetransfer with the pbix file: https://we.tl/t-xgR0AaCQFv
I have two tables with the data I'm using for the chart:
FORMS
-----------
ID
ID_CENTRE
DATE
FORMS_ANSWERS
----------
FORM_ID - FK to FORMS.ID
QUESTION
ANSWER
IS_CHECKED
Some example data would be
FORMS
ID ID_CENTRE DATE
1 1 01/01/2022
2 1 03/01/2022
3 2 01/01/2022
FORMS_ANSWERS
FORM_ID QUESTION ANSWER IS_CHECKED
1 What do you do? Work TRUE
1 What do you do? Study FALSE
1 Are you happy? Yes TRUE
1 Are you happy? No FALSE
2 What do you do? Work TRUE
2 What do you do? Study FALSE
2 Are you happy? Yes FALSE
2 Are you happy? No TRUE
3 What do you do? Work FALSE
3 What do you do? Study TRUE
3 Are you happy? Yes TRUE
3 Are you happy? No FALSE
Before, I had a calculated column on the table FORMS, called "isActive", that marked the last form of echa centre, so:
FORMS
ID ID_CENTRE DATE isActive
1 1 01/01/2022 FALSE
2 1 03/01/2022 TRUE
3 2 01/01/2022 TRUE
and I had that column as a visual filter as you can see in the blue graph of the image below:
But that made that when I filter the date and set it to "02/01/2022", it only shows the count of the answers of the form with ID=3, since it is the only one active. So I changed it to a measure that returns the IDs of the active forms depending on the filtered date. But I don't know how to add this measure as a visual filter, since I am trying to add it but I am not getting the expected results, you can see it on the purple chart:
I can't figure out how to make that measure a visual filter so that it shows the expected results. Or maybe the measure is not correct, could you guide me a bit on the right direction, please?
Thank you for the effort! Regards!
- Fátima
I have tried a couple of additional things:
- Set the visual filter "NEW_isActive" to "if it is not blank". It works if I have the data on a table, but not in the bar chart.
- I have created another measure that returns true or false depending on the other measure, but it does not expand on the visual filters panel, so I cannot mark it to be true.isActiveBoolean = if(not(isblank(FORMS[NEW_isActive])),TRUE,FALSE)
