Unable to filter a card based on a measure?

[Anonymous]

Hi,

I have a card which just shows count of a column values from a table. I have a measure which checks few condition and o/p "YES" or "NO".

I can easily add the measure to any visual or table in the report to filter only values which are "YES" however when I add the same measure as a Visual Level FIlter to the card it does not allow me to make any changes to it. Is that a power BI limitation or am I doing something wrong? 

[Anand24 · Accepted solution]

@Anonymous,

You said you have date slicer from date table while you want scope values to be influenced by it. At the same time, these 2 tables don't have any sort of relationship between them. I wonder without any relationship how the date slicer is going to drive.

Regarding measure filtering a card, the filter getting frozen is actually an expected behaviour. Check this thread and it might be helpful.

Also, there is a possible workaround for filtering that may or may not work in your case.

Here is the work-around:

1. Create a table viz with minimum 3 fields:

i. Field you want to show in Card viz

ii. Measure you want to filter upon

iii. Any related dimension/categorical field (You can once without this field too)

2. Filter on visual level using the measure as you would want to filter the Card; Make sure you filter is such a way so as to bring 1 row in the table; This one row's value should be the value you want to show in your Card

3. Convert table to Card

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anand24]

Hi@Anonymous ,

There shouldn't actually be any issue specific to filtering a visualization.

Here are some things you can do to check though:

1. Make sure report level filter or Page level filter is not over-riding your visual level filter

2. Take a normal slicer(with the measure you want to filter on) and a table(with the field you have in Card viz). Try to filter using these two to check if the measure is able to drive table properly.

3. Make sure the driving by other vizes in page to Card viz is properly taken care of. Use edit interactions to validate this.

It will be much better if you attach sample .pbix or image representation so that community can view your specific issue and debug. The query right now is pretty generic.

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

@Anand24 

Here is a onedrive link : https://1drv.ms/u/s!AtNpxoLLJZQ1axlomBthtHOqM9I for the sample pbix.

If add a card which counts the tasks and try to add a filter on it it doesnt do anything or does not allow you to make any changes however if you add a measure on a visual or table it works perfectly fine.

[v-xuding-msft]

Hi @Anonymous ,

I have reproduced your issue. Do you use .json file to change theme? I find that the card visual is without background by defalut. And the column that we drag into card visual can't change filter type either.  Only the situation that @Anand24 tried, add another date column into filter pane, then it will be changed. Others like measures or columns with other data types can't work.

Please try to turn on the option "Allow users to change filter types"(File > Options and settings > Options > Report settings).

If this can't work for you, please tell us the version of Power BI Desktop that you used and share the steps that you produced. I will submit it as a bug internally.

[Anonymous]

Hi @v-xuding-msft 

That didnt work for me. Apologies if my query were confusing let me explain in detail.

I have a 3 table one with three columns, the second one is a date table and the third one is a simple In scope or Out of Scope Table

Task                                                    Date                        Scope

                            

NOTE none of these tables are connected to each other.

In the report I have a Date Slicer from Date table

and I want the below measure to check few conditions based on the dates selected from the slicer and below measure does that and Outputs if each row is "In scope" or "Out Of Scope"

Activity Scope = 
    VAR _IsSelected = SELECTEDVALUE('Scope'[Scope], BLANK())
    VAR _SelectedStart = MIN('Date'[Date])
    VAR _SelectedFinish = MAX('Date'[Date])
    VAR _EarlyStart = MIN('Task'[StartDate])
    VAR _EarlyFinish = MAX('Task'[EndDate])

    VAR _Table = 
        ADDCOLUMNS(
            'Task',
            "InSelection",
                SWITCH(
                    TRUE(),
                    _EarlyStart <= _SelectedStart && _EarlyFinish <= _SelectedFinish && _EarlyFinish >= _SelectedStart, "In Scope",
                    _EarlyStart >= _SelectedStart && _EarlyFinish <= _SelectedFinish, "In Scope",
                    _EarlyStart >= _SelectedStart && _EarlyStart <= _SelectedFinish && _EarlyFinish >= _SelectedFinish, "In Scope",
                    "Out of Scope"
                )
        )
RETURN
    IF(
        ISBLANK(_IsSelected),
        SELECTCOLUMNS(
            _Table,
            "Selected", [InSelection]
        ),
        SELECTCOLUMNS(
            FILTER(
                _Table,
                [InSelection] = _IsSelected
            ),
            "Selected", [InSelection]
        )
    )

Now let's say based on the O/P of the measure I want to filter Rows which are "In Scope".

I can do so by adding a measure to a visual level filter and manually typing "In scope" as below and it works great.

Now the challenge is when I add a card which basically counts number of TaskName and try to add the same measure and try to edit the "Contains" it doesnt let me do anything its frozen of sorts.

Hope the query is clear. Sorry if I confused you guys.

I've attached the below sample pbix for testing if that helps.

PBI version : 2.83.5894.961 64-bit (July, 2020)

I also did try to Enable the "Allow users to change filter type"

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

@Anand24 is right. The value in card is an aggregate value. It is not supported to filter aggregate value in filter pane.  Sorry for my mistake above. You could try the workaround that @Anand24  suggested or create a measure to count "task name".

Measure = 
CALCULATE (
    COUNT ( Task[TaskName] ),
    FILTER ( Task, [Activity Scope] = "In scope" )
)

[Anand24 · Accepted solution]

@Anonymous,

You said you have date slicer from date table while you want scope values to be influenced by it. At the same time, these 2 tables don't have any sort of relationship between them. I wonder without any relationship how the date slicer is going to drive.

Regarding measure filtering a card, the filter getting frozen is actually an expected behaviour. Check this thread and it might be helpful.

Also, there is a possible workaround for filtering that may or may not work in your case.

Here is the work-around:

1. Create a table viz with minimum 3 fields:

i. Field you want to show in Card viz

ii. Measure you want to filter upon

iii. Any related dimension/categorical field (You can once without this field too)

2. Filter on visual level using the measure as you would want to filter the Card; Make sure you filter is such a way so as to bring 1 row in the table; This one row's value should be the value you want to show in your Card

3. Convert table to Card

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anand24]

@Anonymous,

Worked perfectly fine for me. I created a Card for Count(TaskName) and filtered it by EndDate.

Check the image below:

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anonymous]

@Anand24 This thread is about unable to filter based on a measure. 

I have no issue with adding another column as a filter to a card. I'm refering to a measure as a card filter. Try adding a measure to a card filter and try to edit the filter.

[amitchandak]

@Anonymous , you can have a slicer on measure. But you can have a measure as a filter at the visual level

refer :https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter

[Anonymous]

@amitchandak I cant use a measure as a slicer nor can I use a measure as filter on a card.

[rile14]

@uniquetrooper You can try with the multi-row card. There you can apply a measure as a filter. Just format it a bit and it can look the same as a regular card.

[uniquetrooper]

Hey, Did you find solution to this problem?