Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ianweb100
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
d_gosbell
Super User
Super User

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. 

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.
d_gosbell
Super User
Super User

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. 

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 others find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.