1
votes

In my fact table I have one column which indicates the time difference between two datetime values in integer minutes. I have also created a group (by left-clicking on the [Difference_min] column in the dashboard view and selecting new group) to better display the difference in time intervals. In the table view it looks like this:

enter image description here

I use a simple measure to count the number of rows in my Data table and present it as % of total to see the distribution of time differences based on the interval grouping:

NumberOfRows = COUNTROWS('Data')

enter image description here

First I remove the blank values (Tom) by adding a the [Difference_min] column as a slicer and select all values except the blank value.
Then I want to only look at the percentage of the two highest time difference groups. I duplicate my histogram and in the visual slicer pane I select only 30 - 39 and 40+, then I write a proper percentage measure like this and add it to the new visual:

NumberOfRows_pct = 
DIVIDE(
    [NumberOfRows];
    CALCULATE(
        [NumberOfRows];
        ALL('Data'[Difference_min (group)])
    );
    0
)

I end up with this (green = only countrow function, black = divide function):

enter image description here

The green bars are correct (or at least what I expect to see), though I would have expected the black to have the same value as in the previous diagram, i.e. 7.5 % and 4.8 % respectively.

However, if I remove the filtering on [Difference_min] the NumberOfRows_pct measure behaves as I would expect:

enter image description here

Question
Why does the filtering/slicing on [Difference_min] cancel the effect of the
ALL('Data'[Difference_min (group)]) in the percentage measure?

Pbi example file: pbi file

2

2 Answers

3
votes

What you're seeing is a result of the DAX auto-exist optimization.

To understand this, consider the following queries, which are similar to what Power BI uses to render the bar chart. The first query does not include the criteria that [Difference_min] shouldn't be blank, but the second query does include it:

DEFINE
VAR __DifferenceMinGroupFilter = TREATAS({"30 - 39"} , 'Data'[Difference_min (group)])
VAR __DifferenceMinFilter = FILTER(VALUES('Data'[Difference_min]), NOT ISBLANK('Data'[Difference_min]))

// Query 1 (no filter on [Difference_min]):
EVALUATE
SUMMARIZECOLUMNS(
    'Data'[Difference_min (group)],
    __DifferenceMinGroupFilter,
    "NumberOfRows", [NumberOfRows],
    "NumberOfRows_pct", [NumberOfRows_pct]
)

// Query 2 (with filter on [Difference_min]):
EVALUATE
SUMMARIZECOLUMNS(
    'Data'[Difference_min (group)],
    __DifferenceMinGroupFilter,
    __DifferenceMinFilter,
    "NumberOfRows", [NumberOfRows],
    "NumberOfRows_pct", [NumberOfRows_pct]
)

Here are the results produced by these two queries on your Power BI file, with the issue showing on query #2:

enter image description here

The reason you'd have to use ALL('Data') in this case, is because of the auto-exist optimization which impacts query #2. From the article I linked above:

The auto-exist mechanism kicks in when two or more columns of the same table are filtered together. Instead of using the columns as separate filters, SUMMARIZECOLUMNS generates only one filter which filters all the columns with only the existing combinations of values.

So when the measures are evaluated, you actually have a filter on the [Difference_min]-column which only consists of the values in that column when [Difference_min (group)] is "30 - 39", because both these columns reside on the same table.

For this reason, it's not enough to remove the filter on the [Difference_min (group)] column using ALL. You'd have to modify your measure to something like:

NumberOfRows_pct = 
DIVIDE(
    [NumberOfRows],
    CALCULATE(
        [NumberOfRows],
        ALL('Data'[Difference_min (group)]),
        NOT ISBLANK('Data'[Difference_min])  // Overrides the filter from outside
    ),
    0
)
0
votes

Because the ALL statement in the way you used it only removes the filters applied to the Difference_min (group) column. That's why Difference_min is still effective. Try ALL ( 'Data' ) instead to eliminate all filters applied to the data table. Also see: https://docs.microsoft.com/en-us/dax/all-function-dax