Visual Level Filter ignored by Measure

[murrayfoxcroft]

I have the below set up (see image). The 934 number is from a measure: 

_MeasureTotalSelected = COUNTROWS(VALUES(CUSTOMER))

It's obvious that the 934 does not correlated with the count of the rows in the visual. If I add any slicer on to the page (like the "Active" one), then that slicer is reflected in the total, however, all the visual level filters are just not applied. How can I make the visual level filters apply to the measure? 

[Anonymous]

HI @murrayfoxcroft,

If you want to enable the visual level filter effect, you can try to combine use allselected and countrows functions.

For example:

_MeasureTotalSelected =
COUNTROWS ( ALLSELECTED ( CUSTOMER ) )

BTW, I'm not so sure above formula works if you drag other table column as the filter of current visual.

Regards,

Xiaoxin Sheng

[Anonymous]

I have the same scenario and none of the above has worked.

I have two measures. One factors in the visual level filters and the other does not. Why is this?

Works with visual level filters:

Total Searches = COUNT('Usage Statistics'[ID])

Does not work with visual level filters: 

2017-06 = CALCULATE(COUNTROWS('Usage Statistics'), 'Usage Statistics'[Created] >= date(2017,06,01), 'Usage Statistics'[Created] < date(2017,7,01))

Why does the first one factor in visual level filters but the second one does not?

[Anonymous]

Hello,

Does anybody get a solution for this issue?

Regards,

Parag

[Seth_C_Bauer]

@murrayfoxcroft Is it possible that you need a DISTINCTCOUNT here instead? Power BI will aggregate the visual context to only show the unique rows. Does your raw dataset include that many rows for active customers?

[murrayfoxcroft]

I have made a repro of my scenario - I know what I have in there is not correct to do the job, but I don't know what I need to add in.  There is a PBIX here, and a screen shot below. 

When I move the date slider, the total CountOfMatches adjusts accoringly using the measure below. All happy. 

EndedContracts = CALCULATE(COUNTROWS(Contracts))

However, when I update the visual level filter on the EndedContracts to only show those dates with less than two ended contracts, the total does not adjust acctoringly.  How do I get the CountOfMatches measure to consider the visual level filter applied to the EndedContracts measure?

As per the screen shot below, when I add the filter of < 2 to Ended Contracts, I want the CountOfMatches to become 2. 

[Ashish_Mathur]

Hi,

In my opinion you should not be dragging Ended Matches to the visual filters.  You should instead be dragging Customer there.  Check the box for specific cutomers and you figure will recompute.  Also, you do not need the CALCULATE() function there.  Just this will work

=COUNTROWS(Contracts)

[murrayfoxcroft]

But I dont want to filter on customers. I want to filter on the number of ended contracts. This is a very simple sample of a far more complex system with thousands of customers and contracts. I want to be able to pick out days and months where we lost X amount of customers, or  > X amount of customers. E.g. Show me the days on which we lost more than 100 customers. In this scenario, the total need to tell me on how many days we lost more than 100 customers. I can achieve this on the total of the visual, but given the data volumes we just want a quick view of this number at the top without scrolling every time and having other columns totalled that dont compute well. 

[Ashish_Mathur]

Hi,

Try this measure

=COUNTROWS(FILTER(SUMMARIZE(DateData,DateData[Date],"ABCD",DISTINCTCOUNT(Contracts[Customer])),[ABCD]>2))

This measure will count all dates on which more than 2 customers were lost.  The answer will be one because of all the dates, only on 3 January 2017 did you lost 3 customers.  On the others dates, you only lost one customer each.

The value of 2 which has been hard coded can come via a slicer selection.  But before i do that i just wanted to know if my inderstanding of the problem is correct.  If not, then paste your data and show your expected result.

You may download the file from here.

Will this work?

[murrayfoxcroft]

Yes, that is close, but the 2 needs to come from a visual level filter, or when the visual level filtered is altered, then it needs to impact the total in the measure. 

[Ashish_Mathur]

Hi,

Download the file now.

[murrayfoxcroft]

The slicer works - as per my earlier post, slicers on the page work fine. Go to the visual level filters (under the visualizations tab) on the Contracts matrix. Select some of the dates. The Measure still does not change. 

[Ashish_Mathur]

Hi,

I selected the card visual and then dragged the Date column from the DateDue table to the Visual level filter section.  When i select a date there, the measure responds just fine.