Calculate Sum if / filters based on 2 tables / dax

[Anonymous]

Hello! I have some measure creating experience but am slightly stumped because my data is in two different tables

Here are my two tables 

PGID Table

PGID Cat, Time Type

Vacation, Non-working

Business Development, Working

Bereavement, Non-working

AX Table

PGID Cat, Hours

Bereavement, 10

Vacation, 5

Vacation, 2

Business Development, 15

I would like to create a measure for Working Hours = sum of hours if PGID Cat Table time type is "working".

Does that make sense? I will also make one for non-working hours too of course 🙂 

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can do some steps as follows.

• Solution 1:

1. Create a measure.

_sum=sumx(‘AX’,[Hours])

1. Create a table visual with “PGID Cat” and “_sum” field.

1. Drag the “Time Type” field to the filter on this visual.
   
2. Select “Working” or “Non_working”.

Result:

• Solution 2:

1. Create a measure.

Working = IF(

    MIN('PGID'[ Time Type])="Working",

    SUMX(AX,'AX'[ Hours]))

Non_working = IF(MAX('PGID'[ Time Type])="Non-working",SUM('AX'[ Hours]))

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[DataInsights]

@Anonymous,

Try these measures. A relationship between the tables is required.

Working Hours = 
CALCULATE ( SUM ( 'AX Table'[Hours] ), 'PGID Table'[Time Type] = "Working" )

Non-working Hours = 
CALCULATE ( SUM ( 'AX Table'[Hours] ), 'PGID Table'[Time Type] = "Non-working" )