Forum Discussion
Visual Level Filter ignored by Measure
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.
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)
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.
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?
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.