Power BI Visual Level Filter Skewing Measure

Question

In my current data model, I am training to avoid using bidirectional relationships if at all possible.

In order for my lookup tables to interact and filter each other, I have been using a KPI as a visual level indicator.

Basically if this KPI is greater than 0, it means that data exists.

This has worked perfectly on slicers, but on other visualizations it still shows all possible fields unless I manually select the items in the slicers.

The problem I am running into is that I have a function that gives a daily average workload based on planned working days. These are indicated by a '1' on the Date table.

While normally this works fine, when using a KPI for a visual level filter it is also impacting another measure that is using the dates from my calendar table.

In particular on drill down is where I am getting stuck.

Avg. Daily Workday Runtime = 
DIVIDE( 
    SUMX(
        efficiency_records,
        efficiency_records[Runtime] * related('Calendar'[Binary Working Days])
    ),
    CALCULATE(
        sum('Calendar'[Binary Working Days])
    )
) / 60

The above measure takes the number of days that qualify as planned working days (1 or 0 column next to date) to give the average runtime per planned workday.

The issue is that if I add a filter to only show items that have records, this measure then only looks at the days the specific machine ran, rather than all days in the selected time period.

Adding an allexcept('Calendar', 'Calendar['Date']) seemed to work at first, but if I drilldown on a part or machine then the date table is still getting filtered. As a result, while the true number of working days in the selected range is '11' let's say, it ends up as '2' for a selected machine because it only ran for 2 days.

I feel like there has to be some combination of filters to get around this, but have had a hard time finding the right combination.

Any advice would be appreciated.

I'd recommend sharing a minimal example PBIX file for this (upload to e.g. Dropbox or Google Drive and share the link). It's quite difficult to sufficiently express all of the relevant filters, relationships, measures, etc. — Alexis Olson
I just tried putting together a simplified model of what I thought was the problem but it's not recreating the issue for some reason, works just as I would like it to. This advice has led me to realize that the problem is in how one of the relationships is set up, not anything to do with the DAX. Thank you! — kanderson
Nice. That's a common bonus of constructing minimal examples. — Alexis Olson

kanderson kanderson · Accepted Answer · 2021-01-26T00:37:07

Problem was that I had a secondary measure based on a different table that was showing regardless of what was happening. By applying the filter to that measure instead of the visual itself everything works as intended.

Power BI Visual Level Filter Skewing Measure

1 Answers