DAX - Or in a measure that uses Calculate

[potap]

Hi!

I am trying to filter my Sum calculation with two different columns in my UnitCategory table.
1st column : [isSelfPropelled] which is True () for sedans, trucks, etc.
2nd column : [VehiculeCategory] which is a code that describe every type of vehicule or equipment

Here is what I have in mind but that obviously doesn't work.

NumVehiculeEquivalents:=Calculate(
Sum(factVehiculeRental[VehiculeEquivalents]);
dimCalendar[isCurrentYear] = True(); dimUnitCategory[isSelfPropelled]=True()||dimUnitCategory[VehiculeCategory]="2"||dimUnitCategory[VehiculeCategory]="3"
)

The "2" and "3" mean snow removal equipment and they are not self-propelled. So, I want the total of vehicule equivalents for self-propelled and snow removal equipment.

I encounter this kind of issue quite frequently in other cases. I can get around this problem by adding a calculated column in my dimension table but I would like to find an easier way to solve this.

In this example, I would add a new column in dimUnitCategory with a combination of If() and put that column in the Calculate().

I could also create 1 measure for the self-propelled, an another one for the snow removal equipment and a final one that sums both but I would like something simpler.

Maybe that can't be done but it is worth asking

Thank you!!!

 

[scottsen]

So... the short answer is "Yes", this is totally supported.

I think what is going on here is that you are using the "simple" format to calculate:
=calculate(sum([Sales]), calendar[year] = 2013)

This works lovely, however realize it is basically "short-hand", and the equivalent is:

=calculate(sum([sales]), filter(all(calendar), calendar[year] = 2013))

using this latter format, you can do fancier OR based work.

=calculate(sum([sales]), filter(all(calendar), calendar[year] = 2013 || calendar[year] = 2014))

So... do that

 

[potap]

Thanks for your answer!

This works for a grand total.

=
CALCULATE (
    SUM ( factVehiculeRental[VehiculeEquivalents] );
    FILTER (
        ALL ( dimUnitCategory );
        dimUnitCategory[isSelfPropelled] = TRUE ()
            || dimUnitCategory[VehiculeCategory] = "2"
            || dimUnitCategory[VehiculeCategory] = "3"
    );
    dimCalendar[isCurrentYear] = TRUE ()
)

But when I want to use it in another calculation like cost per vehicule and use a slicer for a specific type of vehicules, it doesn't work. Because of the All() function the costs always get divided by the grand total. It also displays categories I don't want to see (in bold).

Bus 26 465
Crane 26 465
Excavator 26 465
Grader 26 465
Light truck 26 465
Loader 26 465
Other equipment 26 465
Pickup 26 465
Sedan 26 465
Snow removal equipement 26 465
Tool 26 465
Truck 26 465
Van 26 465