cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Date when Score "good" 8/10 Times

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!

1 ACCEPTED SOLUTION
Super User

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

16 REPLIES 16
Super User

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

Helper I

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?

Super User

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.

Helper I

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.

Super User

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?

Helper I

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

Super User

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

Helper I

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!

Super User

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?

Helper I

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

Super User

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?

Helper I

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!!

Super User

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

Helper I
 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

Super User

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.

Helper I

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!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors