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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sarah2
Helper II
Helper II

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. 

sarah2_0-1690461366940.png

 

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

sarah2_1-1690461401328.png

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

Thank you!

1 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

View solution in original post

16 REPLIES 16
barritown
Super User
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.

barritown_0-1690549627609.png

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

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?

@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):

barritown_1-1690551441438.png

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.

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

sarah2_0-1690555478526.png

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

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

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 

@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

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!

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?

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

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?

 

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 

sarah2_0-1690902703547.png

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

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

DateTimeBehaviorSideScoreTrainerAnimal
1/27/20233ArmRight2SarahSheba
1/31/20232ArmRight3SarahSheba
2/8/202312ArmRight3SarahSheba
2/17/202312ArmRight2SarahSheba
4/5/202312ArmRight3SarahSheba
4/25/20232ArmRight3SarahSheba
4/26/20232ArmRight3SarahSheba
5/4/20232ArmRight2SarahSheba
5/19/202312ArmRight3SarahSheba
5/25/202311ArmRight2SarahSheba
5/31/202312ArmRight3SarahSheba
6/9/20232ArmRight3SarahSheba
6/15/202312ArmRight2SarahSheba
6/23/202311ArmRight3SarahSheba
6/29/202311ArmRight2SarahSheba
7/6/202312ArmRight2SarahSheba
7/15/20233ArmRight2SarahSheba
7/18/20235ArmRight2SarahSheba
1/27/20233ArmLeft2SarahSheba
1/31/20232ArmLeft3SarahSheba
2/8/202312ArmLeft3SarahSheba
2/17/202312ArmLeft2SarahSheba
4/5/202312ArmLeft3SarahSheba
4/25/20232ArmLeft3SarahSheba
5/4/20232ArmLeft2SarahSheba
5/10/20232ArmLeft3SarahSheba
5/19/202312ArmLeft3SarahSheba
5/25/202311ArmLeft3SarahSheba
5/31/202312ArmLeft3SarahSheba
6/9/20232ArmLeft3SarahSheba
6/15/202312ArmLeft3SarahSheba


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

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

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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