Search code examples
powerbidaxmeasure

PowerBI Visual Level Filter ignored when using Measures

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

Looking at the diagram, when I move the date slider, the total CountOfMatches adjusts accordingly using the measure below. All happy. 

EndedContracts = COUNTROWS(Contracts)

Screen Shot

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 accordingly.

How do I get the CountOfMatches measure to consider the visual level filter applied to the EndedContracts measure? If I add a total on the visual, this works as expected, but this example is an abstraction of the full solution which deals with 1000s of customers and having a separate total is ideal.

I also tried to move the EndedContracts measure to a Page Level Filter, but PowerBI will not accept it.

Solution

  • Why is Power BI not accepting EndedContracts as a Page Level Filter?

    EndedContracts is a measure, so its displayed value depends on how it is broken down. If you don't break it down at all, then the displayed value is 5. This is what a Card visual would show.

    In your table, the way it is broken down is based on the columns you've chosen: you're filtering by date but not customer. For any given date, the number of EndedContracts is known. On 03 January 2017, there are 3 ended contracts. Filtering the number of EndedContracts to less than 2 will filter this date out.

    To demonstrate that the breakdown is based entirely on the columns in your table, try adding the customer column to your table. If you do so, then the 03 January 2017 will no longer be filtered out. This is because you're now breaking down the number by date and by customer, and there is no date/customer combination with 2 or more EndedContracts so nothing gets filtered out.

    The reason you can't use a measure as a filter in the Card visual or as a page filter is because there's no implicit way for the filter to know how you want to break down the measure. Do you want to filter based on the total? This is very unlikely. Do you want to filter based on the total by date? The total by customer? The total by date and customer? Power BI doesn't know.

    How do you work around this?

    What you need to do is find a way to tell Power BI how to break down the EndedContracts total of 5 in a way that it can filter.

    One way to do this is via a calculated table and the SUMMARIZECOLUMNS statement. In the Modeling tab, select New Table, and then try the following:

    Ended Contracts by Date =
SUMMARIZECOLUMNS (
    Contracts[EndDate],
    "Total Ended Contracts", [EndedContracts]
)

    This will create a calculated table that shows you the EndedContracts number by EndDate (ignoring customer). It's the DAX equivalent of the table in your screenshot. (SUMMARIZECOLUMNS is like GROUP BY in SQL, if you're familiar with SQL.)

    Once you have this calculated table, you can join it to your original Contracts table by date and then use the "Total Ended Contracts" column as a Page Filter. It will behave exactly as you want, filtering out all dates with 2 or more EndedContracts.

    Diagram showing the join between the calculated table and the original table

    Why does this work, but the measure does not? Because unlike the measure, you've clearly specified how you want to break down the measure (in this case by date). You've instantiated the table, and now filtering on it acts like filtering on any other table/column in your data model.

    I hope my walkthrough helps explain why Power BI doesn't behave intuitively, and how to work around it. Let me know if something I've said is confusing. Thanks for providing a link to a sample data model. It was very helpful.

    Sidenote: Why can the CountOfMatches measure not see the visual level filter applied to the table?

    Each visual on your page is considered independent. It's not unreasonable for the same measure to be broken down in different ways on the same page (e.g. by date, by customer, by sales region). Visual level filters are there to give you flexibility between visuals: e.g. you might filter one chart to MTD, but show YTD numbers in another chart.

    Whenever you want a filter to be applied to multiple visuals, you either need to do a page level filter, or apply the same filter to each visual, which is certainly what you were trying to do. Unfortunately, neither a page level filter nor a visual level filter on the Card visual will accept a measure. My solution above will work for both, however.

    References:

    SUMMARIZECOLUMNS: https://msdn.microsoft.com/en-us/library/mt163696.aspx