Count of records following multiple criteria

[Anonymous]

Hello,

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
1	144	VISIT
2	144	CORRECTIVE
3	131	PREVENTIVE
4	131	CORRECTIVE
5	131	CORRECTIVE
6	130	CORRECTIVE
7	105	CORRECTIVE

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

• multiple CORRECTIVE operation performed on the same location the same day count as 1
• if a PREVENTIVE maintenance was performed the same day on the same location, the count of CORRECTIVE should be 0

I tried many different measures but I don't manage to take into account the second criteria. 

CorrCount = CALCULATE(DISTINCTCOUNT('Table1'[Date]), 'Table2'[MAINTENANCETYPE]="CORRECTIVE" && 'Table2'[MAINTENANCETYPE]<>"PREVENTIVE")+0

How would you proceed ?

Thanks in advance.

Regards,

Cado

[selimovd · Accepted solution]

Hey @Anonymous ,

I think I found an approach.

First I summarize the CORRECTIVE and PREVENTIVE rows by location and date. Then I remove the days and location that contain PREVENTIVE rows with the EXCEPT function.

Amount 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
    COUNTX ( vWithoutPreventive, 1 )

Please see my solution file:

https://www.swisstransfer.com/d/2fe784f7-131f-4e06-b418-2b52c3d45b65

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]

Hi @selimovd @amitchandak 

Thanks to both of you for your help, this measure was really starting to give me headaches !

@amitchandak your measure seems to suffer from invalid parameter type errors

@selimovd Your measure works perfectly !

Have a good day,

Cado

[selimovd]

Hey @Anonymous ,

yes, the scenario was not that easy. But I'm happy it works now 😊

If you have any questions just let me know.

Best regards

Denis

[Anonymous]

@selimovd Yes good job !

Actually I have an other issue really close from this one !

In the Table2 I have an additionnal column that gives a duration in decimal number and I want it to be summed following the same criteria.

I tried replacing the COUNTX function of you're measure by 

SUMX(vWithoutPreventive, SUM(Table2[Duration])) but the results are very random, for some locations it returns a tremendous number that is not correct. Do you know what could go wrong with the formula ?

Regards,

Cado

[amitchandak]

@Anonymous , Try like

countx(filter(filter(Summarize(Table2, Table2[MAINTENANCETYPE]="CORRECTIVE" ), 'Table1'[Date], Table[LOCATION], "_1", discount(Table2[ID_BI])),[_1] >=2),[Date])