Sum of records following multiple criteria

[Anonymous]

Hi @selimovd 

To follow on my last topic :

I got 2 tables in relationship through the ID_BI column.

Table1:

ID_BI	LOCATION	DATE
144	A	2021-05-12
131	A	2021-04-29
130	A	2021-04-29
105	B	2021-02-14

Table2:

ID_EQ	ID_BI	MAINTENANCETYPE	DURATION
1	144	VISIT	1
2	144	CORRECTIVE	3
3	131	PREVENTIVE	4
4	131	CORRECTIVE	1
5	131	CORRECTIVE	1
6	130	CORRECTIVE	0.5
7	105	CORRECTIVE	7

The objective is to calculate the sum of corrective maintenance duration based on this criteria :

• multiple CORRECTIVE operation performed on the same location the same day are summed.
• if a PREVENTIVE maintenance was performed the same day on the same location, the sum should be 0

I tried to edit the end of the measure you created for me but it seems not to be working :

Duration sum without PREVENTIVE = 
VAR vBaseTable =
    ADDCOLUMNS (
        Table2,
        "@DATE", RELATED ( Table1[DATE] ),
        "@LOCATION", RELATED ( Table1[LOCATION] )
    )
VAR vPreventive = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "PREVENTIVE" )
VAR vCorrective = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "CORRECTIVE" )
VAR vWithoutPreventive =
    EXCEPT (
        DISTINCT (
            SELECTCOLUMNS ( vCorrective, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        ),
        DISTINCT (
            SELECTCOLUMNS ( vPreventive, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        )
    )
RETURN
    SUMX(vWithoutPreventive, SUM(Table2[DURATION]))

How would you proceed ?

Thanks in advance.

Regards,

Cado

[selimovd]

Hey @Anonymous ,

I think it would be sufficient when you add the sum of duration at the end of your summarized table.
Then you can do the SUMX on that new column.

Try the following measure:

Duration sum without PREVENTIVE = 
VAR vBaseTable =
    ADDCOLUMNS (
        Table2,
        "@DATE", RELATED ( Table1[DATE] ),
        "@LOCATION", RELATED ( Table1[LOCATION] )
    )
VAR vPreventive = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "PREVENTIVE" )
VAR vCorrective = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "CORRECTIVE" )
VAR vWithoutPreventive =
    EXCEPT (
        DISTINCT (
            SELECTCOLUMNS ( vCorrective, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        ),
        DISTINCT (
            SELECTCOLUMNS ( vPreventive, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        )
    )
VAR vResultWithDuration=
    ADDCOLUMNS(
        vWithoutPreventive,
        "@DURATION", CALCULATE(SUM(Table2[DURATION]))
    )
RETURN
    SUMX(vResultWithDuration, [@DURATION])

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hey @selimovd 

Thanks for your help, it seems that this measure still have a problem, for some locations it returns a sum of duration far bigger than the actual sum of numbers in the column (I checked in the table).

Do you have an idea of where the problem could come from ?

Regards,

Cado

[selimovd · Accepted solution]

Hey @Anonymous ,

I also checked, the values per date and location seemed to be OK, but the problem was for sure with the total.

This was because the data linage does only work for the first argument in EXCEPT. So I had to restore the filter context in the measure.

Can you check if that gives you the result you want:

Duration sum without PREVENTIVE = 
VAR vBaseTable =
    ADDCOLUMNS (
        Table2,
        "@DATE", RELATED ( Table1[DATE] ),
        "@LOCATION", RELATED ( Table1[LOCATION] )
    )
VAR vPreventive = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "PREVENTIVE" )
VAR vCorrective = FILTER ( vBaseTable, Table2[MAINTENANCETYPE] = "CORRECTIVE" )
VAR vWithoutPreventive =
    EXCEPT (
        DISTINCT (
            SELECTCOLUMNS ( vCorrective, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        ),
        DISTINCT (
            SELECTCOLUMNS ( vPreventive, "@LOCATION", [@LOCATION], "@DATE", [@DATE] )
        )
    )
VAR vResultWithDuration=
    ADDCOLUMNS(
        vWithoutPreventive,
        "@DURATION",
        VAR rowLocation = [@LOCATION]
        VAR rowDate = [@DATE]
        RETURN
        CALCULATE( SUM(Table2[DURATION]), Table1[LOCATION] = rowLocation && Table1[DATE] = rowDate )
    )
RETURN
    SUMX(vResultWithDuration, [@DURATION])

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd it works !

Congratulations you are the best 😉

Thank you very much for your help,

Cado

[selimovd]

Haha, thank you  @Anonymous  😉

I'm happy it works now!

Best regards

Denis