cancel
Showing results for
Did you mean:
New Member

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

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

2008     Chef

2008     Plumber

2008     Eletrician

2008     Data Analyst

2008     Cleaner

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

2006 + 2007  - the results should be:

2007 + 2008  - the results should be:

Cleaner

2006 + 2008  - the results should be:

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

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

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

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.

4 REPLIES 4
New Contributor

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

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.

Community Support Team

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

Hi @d_gosbell ,

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Contributor

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

@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.

Community Support Team

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

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.