Use measure as a filter

[Fátima]

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:

activeForms = var _max = maxx(filter(ALLSELECTED(FORMS), FORMS[CENTER_ID] = Max(FORMS[CENTER_ID])), FORMS[DATE])

return

CALCULATE(max(FORMS[ID]), filter((FORMS) ,  FORMS[DATE] =_max))

My question is: how can I make the bar chart show just the results for the forms that the measure returns? I have tries to place the measure on the filter section, but nothing happens.

Thanks! Regards!

[amitchandak]

@Fátima , Not very clear. But make that measure as visual level filter

[Fátima]

Hi @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
  
  isActiveBoolean = if(not(isblank(FORMS[NEW_isActive])),TRUE,FALSE)
  , but it does not expand on the visual filters panel, so I cannot mark it to be true.