Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community,
I am currently working on a internal Scoring System for our Customers. For every Action a customer performs, he get's a certain amount of points or some points are deducted. These points are accumulated and I have the raw data and calculated columns for totals and running totals by Date. The points are not calculated every day, but only when the user performs a specific action.
But I am struggeling with a question that sounds rather simple but I cannot find the solution myself. So it would be great to get some help!
Here is what I need: I would love to see the (distinct) count of users above a certain threshold on any specific date. I would like to calculate a Measure or Column that counts how many users are above 100 on 01.01.2018, how many are abote 100 on 10.01.2018, how many are above 100 on 15.01.2018 and so on.
Here is how my dataset looks like:
Date | CustomerID | Score | Rank | Score Running Total |
01.01.18 | 99 | 10 | 1 | 10 |
02.01.18 | 99 | 5 | 2 | 15 |
03.01.18 | 99 | 3 | 3 | 18 |
04.01.18 | 99 | -5 | 4 | 13 |
05.01.18 | 99 | -5 | 5 | 8 |
06.01.18 | 99 | 20 | 6 | 28 |
01.01.18 | 105 | 5 | 1 | 5 |
02.01.18 | 105 | 6 | 2 | 11 |
03.01.18 | 105 | 10 | 3 | 21 |
04.01.18 | 105 | -5 | 4 | 16 |
06.01.18 | 105 | 15 | 6 | 31 |
Would be great if someone could help me here!
Thank you very much!
Solved! Go to Solution.
Hi @undergreat,
here is one way to achive what you are looking for:
I assume that you already have a date table in your modell.
In your data table, you need to add a endDate column:
EndDate =
VAR endDate =
CALCULATE (
MIN ( [Date] );
FILTER (
Table;
Table[StartDate] > EARLIER ( Table[StartDate] )
&& Table[CustomerID] = EARLIER ( Table[CustomerID] )
)
)
RETURN
IF ( ISBLANK ( endDate ); DATE ( 9999; 12; 31 ); endDate )
Then add this as a measure:
numberOfCustomerAboveScore =
VAR limit = 40
RETURN
CALCULATE (
DISTINCTCOUNT ( Table[CustomerID] );
FILTER (
ALL ( 'Table' );
MAX ( 'Date'[Date] ) >= Table[Date]
&& MAX ( 'Date'[Date] ) <= Table[EndDate]
&& Table[Score Running Total] >= limit
)
)
You can use the limit value to decide your treshold.
best regards,
Sturla
Hi @undergreat,
here is one way to achive what you are looking for:
I assume that you already have a date table in your modell.
In your data table, you need to add a endDate column:
EndDate =
VAR endDate =
CALCULATE (
MIN ( [Date] );
FILTER (
Table;
Table[StartDate] > EARLIER ( Table[StartDate] )
&& Table[CustomerID] = EARLIER ( Table[CustomerID] )
)
)
RETURN
IF ( ISBLANK ( endDate ); DATE ( 9999; 12; 31 ); endDate )
Then add this as a measure:
numberOfCustomerAboveScore =
VAR limit = 40
RETURN
CALCULATE (
DISTINCTCOUNT ( Table[CustomerID] );
FILTER (
ALL ( 'Table' );
MAX ( 'Date'[Date] ) >= Table[Date]
&& MAX ( 'Date'[Date] ) <= Table[EndDate]
&& Table[Score Running Total] >= limit
)
)
You can use the limit value to decide your treshold.
best regards,
Sturla
@sturlaws
Thanks so much for your help. Unfortunatel, I cannot make it work and I don't see what I did wrong. Could you please take a look?
hm, I am not able to recreate that behaviour. Is there a relationship between the '03 - Score calculation' and the 'Calendar'-table?
Some other possible issues:
In the EndDate formula, I have made a typo CALCULATE( MIN([Date])... should be changed to CALCULATE( MIN('03 - Score calculation'[Date])..., but this should not be causing any issues.
In numberOfCustomersAboveScore the line
regards,
Sturla
EDIT: What data field did you put in the table? It should be the date from the Calendar-table, not the date from '03 - Score calculation'
Thanks so much. It's a bit emberassing to tell: But the problem was the connection between the data and the calendar table. Works like a charm. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |