count the amount of people in each category based on each persons latest sample with slicers

[Anonymous]

Hi all,

I've been looking for some answer to this for a good while now and tried several different options.

Basically I would need the following:

1. Find each persons latest sample in current timeline (works in a table with the person_identifier)
   1. Uses Maxx('test', 'test[person_sample_order])
2. Calculate how many people there are per each category of latest sample time
   1. Here it always includes the different categories, hence it's the same as a distinctCount over groups

A concrete example in the picture below. 

I have selected person_id 118 for this example and the sample_time is between 10/10/2018 and 11/3/2018:

In this timeline this person has the following samples taken 118_18, 118_19, 118_20, 118_21, 118_22, 118_23 (seen in table 1a) where 118_23 (seen in table 1b) is the latest_sample taken. 

a normal distinctcount of person_identification over category (bar 2a) shows all the various samples, the person has had over the timeline... My other tries has only reproduced this result as seen in bar 2b and whne I try to get the latest_sample using a filter I get the result in 2c...

The target here would be that person 118 would only be counted in the 45-59 category as that is the category of sample 118_23 and not for the other categories at all. So a person should only be counted once in the graph.

So far I've tried the following:

- Table dax (summarize, summarizecolumns, etc) -- however then slicers don't work as tables are calculated on model load...

- All/Allselected on category-column and also splitting category to its own table and trying to use all/allselected on that.

- Get the measure to get the category for me, though measures can't be used as legends/axis.

Does anyone have some idea if this can be done at all? It's easy to do statically, but I have no idea how to do it, so that the slicer also works...

Thanks a lot in advance and do tell if there's something missing in my description!

Here's a sample file.pbix where I took the above picture from if someone wants to try some magic.

[v-yuta-msft]

Hi MatiasM,

Seems like your requirement is to change columns based on the slicer selection? Could you clarify more details about your requirement?

Regards,

Jimmy Tao

[Anonymous]

Hi v-yuta-msft,

Thanks for the quick reply. I'll try to clarify in a more general way.

What I need is for the graph to only account for the row of the last event for each ID and ignore all other rows for that ID. Then from that filtered table calculate amount of unique IDs per category from another column in the table. 

Hence when I have a slicer selection, I want the graph to only account for each Individual IDs last event within the selection.

E.g. in the case below, my slicer selection returns the following table to be used in the graph.

ID    -  Event order - Category

118  -          1         -    A

118  -          2         -    D

118  -          3         -    B

119  -          1         -    E

119  -          2         -    C

So when the table includes this information the bar chart should only account for the category of the largest event order (3 for ID 118 and 2 for ID 119). When the source has been filtered to only include the last event order the table would look as following:

ID    -  Event order - Category

118  -          3         -    B

119  -          2         -    C

When the table is filtered I want to do a normal bar chart using the "Category" column as category and Y-axis would be the distinct count of ID. The final result would be the following:

Wanted result:

B = 1

C = 1

However, currently the graph calculates the last event for each category per ID, hence the result is the same as a distinct count of ID over Category. resulting in the following:

Current result:

A = 1 

B = 1

C = 1

D = 1

E = 1

Thank you very much for your time and hope I make more sense now!

//MatiasM

[Anonymous · Accepted solution]

Hope this helps someone in the future.

I haven't come up with a solution for this in DAX, so I decided to go with an R-script instead. I skipped the order column and used the date field directly.

Downside with this solution is that you cannot export the summarized table, only the datafields =( Also interactivity and tooltip is gone... So not optimal... But does the job required.

If someone comes up with a nice solution for DAX, that would be awesome. But until then, this script works great for me =D

Have a good week people!

Input for r-script is non-summarized ID, date, category

R-script

#rename dataset to be handled more easily
names(dataset) <- c("Identity", "Date", "Category")

#manipulate date to be date (has to be date field in PowerBI)
dataset$Date<- as.Date(as.character(dataset$Date))


#Look for maximum date per ID and retrieve the category
temp = aggregate(Date~ Identity, data = dataset, max)

df = merge(dataset, temp, by=c("Identity", "Date"))

#Count unique IDs per category
df = aggregate(Identity~ Category, data=df, function(x) length(unique(x))) df = df[ order(-df$Category), ]



#adjust ylimit to largest category
ylim = c(0, 1.1*max(df$Identity))



#calculate percent of grandtotal rounded to nearest percent
#this will be shown in the datalabel
df$PoGT = round(df$Identity / sum(df$Identity ),2) * 100



#remove border from bars

par(lty = 0)

# Create a simply blue bar graph

plot = barplot(height = df$Identity, ylim = ylim, col="#6699cc")

#Add datalabels
text(x = plot, y = df$Identity, label = paste(df$Identity, paste(df$PoGT, "%"), sep= ", "), pos = 3, cex = 1, col = "black")

#format x-axis to look better Change las = 2 if you have lots and lots of categories
axis(1, at=plot, labels=df$Category, tick=FALSE, las=1, line=-0.5, cex.axis=1.2, col = 'grey')

#format y-axis to look better
axis(2, col = "grey")