How to calculate (measure) the difference between two or more groups of strings

[ianweb100]

Hi all,

I have a time series data set, the data sits all in one file. The data shows all the occupations that existed in a particular year.  What i need to do is to create a way to when select particular years to find which occupations have appear or disappear as the year changes. 

I have tried to adventure to use DAX language , but i could not reach the result wanted.

Please see below a small sample of the data, and the expected results.

Year      Occupation

2006     Chef

2006     Plumber

2006     Eletrician

2006     Data Analyst

2007     Chef

2007     Plumber

2007     Eletrician

2007     Data Analyst

2007     Business Manager

2008     Chef

2008     Plumber

2008     Eletrician

2008     Data Analyst

2008     Business Manager

2008     Cleaner

So, lets say we create a bar chart so select the years, when selecting:

2006 + 2007  - the results should be:

Business Manager

2007 + 2008  - the results should be:

Cleaner

2006 + 2008  - the results should be:

Business Manager

Cleaner 

Overall I found lots of info in using ALLEXCEPT function in DAX but most of the time is for numerical values not for strings.

Any help will be greatly appreciated.

Regards,

Ian

[v-frfei-msft]

Hi @ianweb100 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[d_gosbell · Accepted solution]

So if you create a measure like the following

Changed Occupations = 
var _firstYear = minX(ALLSELECTED(Table1[Year]), Table1[Year])
var _secondYear = MAXX(ALLSELECTED(Table1[Year]), Table1[Year])
var _rowsInFirstYear = CALCULATE(COUNTROWS(Table1), filter(values(table1[Year]), table1[Year] = _firstYear))
var _rowsInSecondYear = CALCULATE(COUNTROWS(Table1), filter(values(Table1[Year]),  table1[Year] = _secondYear))
var _result = IF (HASONEVALUE(Table1[Occupation]) && _rowsInFirstYear = 0 && _rowsInSecondYear > 0 
   , "New"
   , IF (HASONEVALUE(Table1[Occupation]) && _rowsInFirstYear > 0 && _rowsInSecondYear = 0, "Missing" ))
return _result

Then create a table with Occupation and this measure on it and add a filter for years it will only list New or Missing occupations. Other occupations that exist in both the first and second year will not be shown as the measure will return a blank for those and most visuals in Power BI exclude blank values. 

[v-frfei-msft]

Hi @d_gosbell ,

One sample for your reference.

1, Insert an index column in power query.

2. To create two measures as below.

Measure = CALCULATE(MAX(Table1[Index]),ALLEXCEPT(Table1,Table1[Year]))

Measure 2 = 
CONCATENATEX(FILTER(Table1,Table1[Index]=[Measure]),Table1[Occupation],"&")

Regards,

Frank

[d_gosbell]

@v-frfei-msft I think @ianweb100  wanted the output on separate rows, but your code is fine if they want to include the output on a single line.