Search code examples
powerbidaxbusiness-intelligencemeasure

Calculate the difference between 2 rows in PowerBI using DAX

I'm trying to complete something which should be quite simple but for the life of me, I can't work it out. I'm trying to calculate the difference between 2 rows that share the same 'Scan type'.

I have attached a photo showing sample data from production. We run a scan and depending on the results of the scan, it's assigned a color. I want to find the difference in Scan IDs between each Red scan. Using the attached Photo of Sample data, I would expect a difference of 0 for id 3. A difference of 1 for id 4 and a difference of 10 for id 14.

I have (poorly) written something that works based on the maximum value from the scan id.

I have also tried following a few posts to see if I can get it to work..

     var _curid= MAX(table1[scanid])
     var _curclueid =  MAX(table1[scanid])
     var _calc =CALCULATE(SUM(TABLE1[scanid],FILTER(ALLSELECTED(table1[scanid]),table1[scanid]))
    
    return if(_curid-_calc=curid,0,_curid-_calc)

Edit; Forgot to mention I have checked threads; 57699052 61464745 56703516 57710425

Solution

  • Try the following DAX and if it helps then accept it as the answer.

    1. Create a calculated column that returns the ID where the colour is Red as follows:

       Column = IF('Table'[Colour] = "Red", 'Table'[ID])

    2. Create another column as following:

       Column 2 = 
 VAR Colr = 'Table'[Colour]
 VAR SCAN = 'Table'[Scan ID]

 VAR Prev_ID = 
      CALCULATE(MAX('Table'[Column 2]), 
      FILTER('Table', 'Table'[Colour] = Colr && 'Table'[Scan ID] < SCAN))

 RETURN 
     'Table'[Column] - Prev_ID

    3. Output:

    Output

    EDIT:-

    If you want your first value(ID3) to be 0 then relace the RETURN line with the following line:

    IF(ISBLANK(Prev_ID) && 'Table'[Colour] = "Red", 0, 'Table'[Column] - Prev_ID )

    This will give you the following result:

    Another output