Need support on DAX calculate function with multiple filters

[Dave1972]

Hi All,

I hope you ncan help me out....I have fact table with the following information and columns.

measure = sum of load	loadgroup_code	unloadgroup_code	type of shipment
...	blank / empty	blank / empty	blank / empty
...	1	1	1
...	2	2	2
...	3	3	3
...	4	4	4
...	1	blank / empty	8

I want to create 3 measures Sum of load for:

1. P = loadgroupcode = 2 AND unloadgrupcode is not 2 or not blank / empty AND Type of Shipment is not 2 or not blank / empty
2. E = loadgroupcode = 3 AND Type of Shipment is not blank / empty
3. Z = loadgroupcode = 1 AND unloadgrupcode is not 1 or not blank / empty AND Type of Shipment is not blank / empty
4. And in addition one measure P+ E+ Z for the for the sum of load for the combination of P + E + Z.

I started like DAX formula below but is not giving me the correct filter... and cannot find solution in community.

P = CALCULATE([sum of load],

    'Table'[Loadgroup_code] IN { "2" } && 'Table'[Unloadgroup_code] IS <> "2" && 'Table'[Unloadgroup_code] IS <> BLANK && 'Table'[Type of Shipment] IS <> "2" && 'Table'[Type of Shipment] IS <> BLANK

)

Pse can someone help to explain how to write the 4 measure in total (3 for each group and 1 total). Thanks in advance, appreciated

[Anonymous · Accepted solution]

Hi @Dave1972 ,

In DAX, you could use <>  to replace "is not" , && to replace "and" , || to replace "or",  

So please try the following formula:

P =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "2"
            && ( 'Table'[unloadgroup_code] <> "2"
            || 'Table'[unloadgroup_code] <> "blank / empty" )
            && ( 'Table'[type of shipment] <> "2"
            || 'Table'[type of shipment] <> "blank / empty" )
    )
)

E =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "3"
            && 'Table'[type of shipment] <> "blank / empty"
    )
)

Z =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "1"
            && ( 'Table'[unloadgroup_code] <> "1"
            || 'Table'[unloadgroup_code] <> "blank / empty" )
            && 'Table'[type of shipment] <> "blank / empty"
    )
)

P+E+Z = [P]+[E]+[Z]

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Dave1972 ,

It depends on the data type of columns. I have built a data sample for you to easily understand the difference in DAX.

1.If data type of these columns are Text, use "" to replace "blank /empty"

Data type is Text = CALCULATE(SUM(Test[Load]),FILTER('Test','Test'[Text]<>""))

2. If data type of these columns are Number, use BLANK() to replace "blank /empty"

Data type is Number = CALCULATE(SUM('Test'[Load]),FILTER('Test','Test'[Number]<>BLANK()))  

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dave1972]

Hi @Eyelyn9, 

Thanks so much. It works now fine, however how do I handle instead of text "blank /empty" the fact that this cell is just empty....
Best regards, Dave

[Anonymous · Accepted solution]

Hi @Dave1972 ,

In DAX, you could use <>  to replace "is not" , && to replace "and" , || to replace "or",  

So please try the following formula:

P =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "2"
            && ( 'Table'[unloadgroup_code] <> "2"
            || 'Table'[unloadgroup_code] <> "blank / empty" )
            && ( 'Table'[type of shipment] <> "2"
            || 'Table'[type of shipment] <> "blank / empty" )
    )
)

E =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "3"
            && 'Table'[type of shipment] <> "blank / empty"
    )
)

Z =
CALCULATE (
    [Sum of Load],
    FILTER (
        'Table',
        'Table'[loadgroup_code] = "1"
            && ( 'Table'[unloadgroup_code] <> "1"
            || 'Table'[unloadgroup_code] <> "blank / empty" )
            && 'Table'[type of shipment] <> "blank / empty"
    )
)

P+E+Z = [P]+[E]+[Z]

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.