cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianweb100 New Member
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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell New Contributor
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
d_gosbell New Contributor
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
Community Support Team

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

Hi @d_gosbell ,

One sample for your reference.

 

1, Insert an index column in power query.

inde.PNG

 

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],"&")

Capture.PNG

 

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.
d_gosbell New Contributor
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
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.