How to compare two values in different columns and different rows, related to an ID

[Anonymous]

I'm starting to learn Power BI and metrics with DAX and some help will be apreciated, please. I would need to know how, in a sorted table (by ID, Modification and Date), of data already defined by fields ([ID, Modification, Fecha(=Date), V_INICIAL, V_FINAL, RESULT]), I can compare values ​​of different rows and columns, regardless of the number of iterations. My objective is to calculate a measure that returns the Number of different IDs per type of Modification, which meet the "OK" Result. This result compares, for each value of the Modification field, the value of the V_INITIAL of the first iteration, with the value of the V_FINAL of the last iteration, of the Modification. And if both values ​​are different, the Result is "OK", since the modification has been made. Otherwise it is "NOT_OK" because there is no modification. Thank you very much for help! 😉

[Anonymous · Accepted solution]

HI @Anonymous,

It sounds like a common value compares operation between start, end date based on category groups. If this is a case, you can refer to the following measure formula to extract the first and last value based on current category group.

Measure =
VAR filtered =
    CALCULATETABLE (
        table,
        ALLSELECTED ( table ),
        VALUES ( table[ID] ),
        VALUES ( table[Modification] )
    )
VAR _first =
    MINX ( filtered, [Fecha] )
VAR _last =
    MAXX ( filtered, [Fecha] )
RETURN
    IF (
        MINX ( FILTER ( filtered, [Fecha] = _first ), [V_INICIAL] )
            = MAXX ( FILTER ( filtered, [Fecha] = _last ), [V_FINAL] ),
        "NOT_OK",
        "OK"
    )

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @Anonymous,

It sounds like a common value compares operation between start, end date based on category groups. If this is a case, you can refer to the following measure formula to extract the first and last value based on current category group.

Measure =
VAR filtered =
    CALCULATETABLE (
        table,
        ALLSELECTED ( table ),
        VALUES ( table[ID] ),
        VALUES ( table[Modification] )
    )
VAR _first =
    MINX ( filtered, [Fecha] )
VAR _last =
    MAXX ( filtered, [Fecha] )
RETURN
    IF (
        MINX ( FILTER ( filtered, [Fecha] = _first ), [V_INICIAL] )
            = MAXX ( FILTER ( filtered, [Fecha] = _last ), [V_FINAL] ),
        "NOT_OK",
        "OK"
    )

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks a lot, Xiaoxin.

It's working properly but worse the performance when i'm including the MEASURE into another one:

MEASURE_2 = CALCULATE(DISTINCTCOUNT('Table'[Id]),OR('Table'[Modification]="BACKUP",'TABLE'[Modification]="VARIABLE"),FILTER(Table,[MEASURE]="OK"),USERELATIONSHIP(Table[Fecha],'Date'[Date]))

When I use the MEASURE_2 in a dashboard, the charge is very slow. Sure, maybe I'm doing something wrong, using your MEASURE as a filter.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.