Slicing columns in matrix are producing wrong result if there are multiple selections

[Anonymous]

Hi

I have a problem with understanding how slicing a matrix is working in PowerBI.

I have a table showing Allocations of roles and Capacity for each quarter. I have built a measure that will only show rows for which the allocations exceeds the capacity and its working just fine but only if i inspect on quarter at the time in the matrix with the slicer (see the pictures)

However if i want to show multiple columns (in this case Q3, and Q4) it can be seen that the role "Operational indkøber" is excluded for some reason in Q4

Im looking forward to finding a solution

Cheers

Results for Q3

Correct values for Q3

Results for Q4

Correct values for Q4Results for Q3+Q4 here Operational indkøber is now missing When selecting both "Operationel indkøber" is missing

Here the measure for my filter can be seen, and its values included in the matrix where the desired values can be observedMatrix without the filter applied to rows where allocations exceeds capacity. It can be seen that "Operational Indkøber" Has the value 1 in Q4 and should show whenever Q4 is included in the slicing

The data model used:

[Anonymous]

Hi AntonioM

Thanks for the great reply. I believe you are correct in how my original measure/filter works, thanks for pointing it out.

I have tried implementing your proposed solution, however the result is the same as before. I think i might be due to the Calender_table Year-qtr, but im not sure.

Is this how you though of implementing it?

Cheers

New MeasureMeasure of calender tableRelation for calender table

[AntonioM]

Could you try it without the variables? Instead of allocation and capacity you would write the two expressions (  SUM(allocationSlices[items....  ) out in full inside the IF.

Variables are calculated once, and then their value is fixed for the rest of the measure (so they're really constants). I think the measure is doing the same as before, because the variables don't recalculate for each value of YearQtr, so it just looks at the overall sum.

[Anonymous]

Hi Antonio, thansk for the swift reply, However the result is still the same 😞Same result

[AntonioM]

I think the trouble is that for "Operational indkøber", the total allocation in Q3 and Q4 is less than the total capacity (4076+4375 < 4392+4257). This means your measure filters it out completely (regardless of the comparison in the individual quarters).

You could try something like this, which will check if there are any quarters for which the allocation exceeds capacity. You might need to change some of the tables/columns but I used:

IF(
    CONTAINS(
        ADDCOLUMNS(
            VALUES('Date Table'[Quarter and Year]),
            "temp",
            IF ( [Allocation] > [Capacity] , 1)
        ),
        [temp],
        1
    ),
    1,
    2
)

The logic is:

• Look at the column of quarters visible (or whatever time period you want to check over).
• Add a column which has '1' for any quarters where allocation > capacity.
• Return 1 there is a '1' in this new column, or 2 if there are none.

You should be able to use this to filter the matrix and it will show rows where the total allocation is lower, but an individual quarter is higher. 

One quick other point, where you have the CALCULATE in your measure, the variables allocations and capacity have already been determined, so are not affected by the ALL(). That's why I've not used variables for [Allocation] or [Capacity] in my measure, as they won't recalculate for each different quarter. You could either write separate measures for them or write the full SUM(allocation[allocationhours]) for both.

Hope that helps.