Date when Score "good" 8/10 Times

[sarah2]

Hello! I'm not sure if this is possible, but I'm hoping you all have a better way to think about this:

I have a dataset that has date and a score (1, 2, or 3). I would like power bi (or excel if needed) to tell me the date when the score has been at a "3" for 8 out of 10 days. In this example, the green is where the 8/10 first occurs, and I would like the date 1/27 to be what the program tells me. 

My real dataset is more complicated with different categories and companies so my eventual goal would be a table like this: 

(the full table has columns for Company and Category, above example is simplified)

Thank you!

[barritown]

Hi @sarah2,  

If your full table looks like the one on the screenshot below, you can try to solve your case with the measure provided on the same screenshot and in plain text below.

Good Date = 
VAR _tbl = ADDCOLUMNS ( 
            Data, 
            "Counter", 
            VAR CurrentDate = [Date]
            VAR CurrentCompany = [Company]
            VAR CurrentCategory = [Category]
            RETURN COUNTROWS ( FILTER ( ALL ( Data ), [Date] <= CurrentDate && [Date] >= CurrentDate - 9 && [Company] = CurrentCompany && [Category] = CurrentCategory && [Score] = 3 ) ) + 0 )
RETURN MINX ( FILTER ( _tbl, [Counter] = 8 ), [Date] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[sarah2]

Thank you so much for taking the time to come up with this! 

I have two questions/concerns:
1. This solution would count when there are 8 "3"s, but in that case based on my original example it would give me Jan 18th?
2. In my real data, the dates are not every day, would that effect this formula?

[barritown]

@sarah2,

The solution scans 10 days windows so:

1. It returns Jan 27th with the data from your test case (see A + AA combination below):

2. If there are gaps in dates, some of the analyzed windows will contain 10 days but there will be less than 10 values. In this case, there is a need to add an additional column with index and analyze 10s by index.

[sarah2]

Thank you for your quick responses! I'm getting a little confused on how to do the index to make it work. My data actually has a lot more variables to it. I'm an animal trainer so the columns I'm looking at are Date/Time/Behavior/Side (left/right/not applicable)/Score/Trainer/Animal. I also have a concatenate that combines Date, Time, Trainer and Animal which is the "Session ID". 

The goal is to know when "Me" has gotten a "3" for a specific behavior 8 out of 10 sessions with the specific animal.

[barritown]

@sarah2, 

More questions then. 🙂

Am I correct that you need find this KPI for each combination Animal - Trainer - Behavior - Side or just for Animal - Behavior - Side? The last phrase in your message tells me that it should be the first combination but could you please confirm that you want to analyze your activity as non-related to other trainers?

One animal can be trained only once within an hour, right?

[sarah2]

Yes Animal - Trainer - Behavior - Side !

A trainer could train with different animals in the same time period (I could train with A and B at 11), and one animal could be trained by different trainers in the same period (Me and Trainer2 could train with A at 11) 🙃 the session ID would differentiate those sessions 

[barritown · Accepted solution]

@sarah2,

Here's PBIX - https://www.dropbox.com/scl/fi/zdbrbk94hgc1swvtdoqbo/community-animals-8-10.pbix?rlkey=1xoef3k9p4v4k... - with a step-by-step solution.

First, we create three calculated columns [1.DateTime], [2.ID], [3.Counter], then we summarize our table and create a new column "Good Date" there.

Check it out. If it works, it is possible compress this solution into a measure.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[sarah2]

Sorry for more follow-up. I tried the steps in my document but it couldn't load and was saying it was too much memory. 
You said it is possible to compress it into a measure-- how would I do that?

Thanks!

[barritown]

Unfortunately, the compression I mentioned won't help with such an issue.

How many entries are there in your dataset?

On which step do you have this error? 2 or 3?

[sarah2]

Dang! There's like a million rows of data....
It messed up on step 2

[barritown]

Huh, you've trained a lot of animals. 🙂

Let's try this one on the second step with your millions of rows: 

2.ID = 
VAR CurrentTrainer = [Trainer]
VAR CurrentBehavior = [Behavior]
VAR CurrentSide = [Side]
VAR CurrentAnimal = [Animal]
RETURN RANKX ( FILTER ( TData,
                   [Animal] = CurrentAnimal &&
                   [Side] = CurrentSide &&
                   [Behavior] = CurrentBehavior &&
                   [Trainer] = CurrentTrainer ),
               [1.DateTime], , ASC , Dense )

I guess it won't work so a couple of other questions to think of a workaround maybe:

1) Where do you store your dataset? Excel, some SQL database?

2) As far as I understood, you're not interested in measuring this KPI for other trainers. Is that right? If so, how would you estimate the percentage of the dataset where the trainer is you?

[sarah2]

Okay we are getting close now!
That change to step 2 worked and I was able to finish all of the steps!
The problem now is that it will tell me the date it got to 8 times at "3" but not necessarily out of 10 sessions. For example, (this is filtered for the same animal/trainer and is in chronological order), there are 8 "3"s but they are inturrupted by 3 "2"s 

The summarized table is telling me the last row is the date, but it is not 8 out of 10 sessions

1. We currently have our datasets in 30 excel sheets and uploaded the folder to power bi to combine them. I could look into doing a database instead but I haven't done that before. 
2. I do want to use a slicer for other trainers too which I am able to do with this calculated table!

Thank you!!

[barritown]

Any chance you could recreate a toy dataset with this problem?

[sarah2]

Date	Time	Behavior	Side	Score	Trainer	Animal
1/27/2023	3	Arm	Right	2	Sarah	Sheba
1/31/2023	2	Arm	Right	3	Sarah	Sheba
2/8/2023	12	Arm	Right	3	Sarah	Sheba
2/17/2023	12	Arm	Right	2	Sarah	Sheba
4/5/2023	12	Arm	Right	3	Sarah	Sheba
4/25/2023	2	Arm	Right	3	Sarah	Sheba
4/26/2023	2	Arm	Right	3	Sarah	Sheba
5/4/2023	2	Arm	Right	2	Sarah	Sheba
5/19/2023	12	Arm	Right	3	Sarah	Sheba
5/25/2023	11	Arm	Right	2	Sarah	Sheba
5/31/2023	12	Arm	Right	3	Sarah	Sheba
6/9/2023	2	Arm	Right	3	Sarah	Sheba
6/15/2023	12	Arm	Right	2	Sarah	Sheba
6/23/2023	11	Arm	Right	3	Sarah	Sheba
6/29/2023	11	Arm	Right	2	Sarah	Sheba
7/6/2023	12	Arm	Right	2	Sarah	Sheba
7/15/2023	3	Arm	Right	2	Sarah	Sheba
7/18/2023	5	Arm	Right	2	Sarah	Sheba
1/27/2023	3	Arm	Left	2	Sarah	Sheba
1/31/2023	2	Arm	Left	3	Sarah	Sheba
2/8/2023	12	Arm	Left	3	Sarah	Sheba
2/17/2023	12	Arm	Left	2	Sarah	Sheba
4/5/2023	12	Arm	Left	3	Sarah	Sheba
4/25/2023	2	Arm	Left	3	Sarah	Sheba
5/4/2023	2	Arm	Left	2	Sarah	Sheba
5/10/2023	2	Arm	Left	3	Sarah	Sheba
5/19/2023	12	Arm	Left	3	Sarah	Sheba
5/25/2023	11	Arm	Left	3	Sarah	Sheba
5/31/2023	12	Arm	Left	3	Sarah	Sheba
6/9/2023	2	Arm	Left	3	Sarah	Sheba
6/15/2023	12	Arm	Left	3	Sarah	Sheba

In this example, I would like it to tell me Left Arm is good but not Right Arm

[barritown]

@sarah2,

Sorry, I didn't notice your instant answer last week for some reason.

But I noticed it earlier today and have just tried to reconstruct the problem - no luck, the left arm is correctly determined as the good one with the right date (see here - https://www.dropbox.com/scl/fi/zdbrbk94hgc1swvtdoqbo/community-animals-8-10.pbix?rlkey=1xoef3k9p4v4k...).

I'd recommend you to double check that you didn't miss something related to [Side] in the step 3 and share with me a broken PBIX if you fail to find the root cause. 

[sarah2]

I can't express how exciting this is and how many people are really going to benefit from it!!! I will tinker with it tomorrow and "accept as solution" if it works for me! Thank you so much for your help!