Warm tip: This article is reproduced from stackoverflow.com, please click
dax powerbi

A measure which flags a change based on time

发布于 2020-03-27 10:22:44

I have a table like below with three columns: Year, Type1, and Type2. I need a measure that can flag a change in Type2 for each Type1 over year. A measure that gives a column like Change in Type2 below:

enter image description here

For example, this measure could give 1 if Type2 in Type1 has changed next year, and it gives 0 if Type2 stays the same so that at the end I can have this table:

enter image description here

Is there any measure that can calculate this? Many thanks in advance

Questioner
Hassi
Viewed
59
分享
Alexis Olson 2019-07-03 22:25

You can do the change in Type2 by comparing versus the previous year:

Change in Type2 =
VAR CurrType2 = SELECTEDVALUE ( Table1[Type2] )
VAR CurrYear = SELECTEDVALUE ( Table1[Year] )
VAR PrevType2 =
    CALCULATE (
        SELECTEDVALUE ( Table1[Type2] ),
        ALL ( Table1[Type2] ),
        Table1[Year] = CurrYear - 1
    )
RETURN
    IF ( ISBLANK ( PrevType2 ), BLANK(), IF ( CurrType2 = PrevType2, 0, 1 ) )

The sum of the change is then pretty simple:

Change Sum = SUMX ( VALUES ( Table1[Type1] ), [Change in Type2] )
Hassi 2019-07-06 03:20:10

Thank you very much for taking your time. It worked nicely!

Related issues

1
Displaying additional data below matrix
2
DAX Filter context
3
DAX: please check if I understood the use of Variabes in DAX correctly
4
DAX
5
Does power BI import mode require more memory compared to direct query and live connection mode?
6
How does CALCULATE apply all the filters?
7
Mapbox Power BI circle drill through
8
Top 10 cost where profit is < zero
9
Datasets storage limit and memory limit in Power BI with respect to compression
10
Wildcard search in the IN operator