Forum Discussion

FabioC86's avatar
FabioC86
New Member
4 years ago
Solved

Calculate difference in statuses

Hi all,

I have a dataset table like the one below:

IDInitial StatusFinal Status
a1xx
a2yx
a3zy

 and I have to create a visualization like this one:

Initial StatusDifference
x(number of IDs with final status = x) - (number of IDs with initial status = x)
y(number of IDs with final status = y) - (number of IDs with initial status = y)
z(number of IDs with final status = z) - (number of IDs with initial status = z)

 

How can I calculate the difference? Thank you in advance.

 

Fabio

DAX
Difference
status change
  • johnt75's avatar
    johnt75
    4 years ago

    You can create a measure like

    Difference =
var currentStatus = SELECTEDVALUE( 'Table'[initial value])
var numFinalStatus = CALCULATE( COUNTROWS('Table'), REMOVEFILTERS('Table'[initial status]), 
   'Table'[final status] = currentStatus )
var numCurrentStatus = COUNTROWS('Table')
return numFinalStatus - numCurrentStatus

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You can create a measure like

    Difference =
var currentStatus = SELECTEDVALUE( 'Table'[initial value])
var numFinalStatus = CALCULATE( COUNTROWS('Table'), REMOVEFILTERS('Table'[initial status]), 
   'Table'[final status] = currentStatus )
var numCurrentStatus = COUNTROWS('Table')
return numFinalStatus - numCurrentStatus
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    please check the below picture and the attached pbix file.

     

     

    Difference measure: =
IF (
    HASONEVALUE ( Data[Initial Status] ),
    COUNTROWS (
        FILTER ( ALL ( Data ), Data[Final Status] = MAX ( Data[Initial Status] ) )
    )
        - COUNTROWS (
            FILTER ( ALL ( Data ), Data[Initial Status] = MAX ( Data[Initial Status] ) )
        )
)