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.
Suppose I have two tables, Widgets and Defects. They are related on WidgetID (one to many) as shown.
Widgets
WidgetID | SerialNo | ProductName |
1 | 100 | Alpha |
2 | 101 | Alpha |
3 | 102 | Alpha |
4 | 500 | Beta |
5 | 501 | Beta |
6 | 502 | Beta |
7 | 503 | Beta |
8 | 504 | Beta |
9 | 990 | Delta |
10 | 991 | Delta |
Defects
DefectID | WidgetID | InspectionDate | DefectType |
100 | 1 | 1/1/2021 | Peeling |
101 | 1 | 1/1/2021 | Peeling |
102 | 1 | 1/1/2021 | Peeling |
103 | 1 | 1/3/2021 | Cracking |
104 | 2 | 1/1/2021 | Peeling |
105 | 2 | 1/1/2021 | Peeling |
106 | 3 | 1/1/2021 | Chipping |
107 | 3 | 1/1/2021 | Cracking |
I can easily write a DAX measure to give me DefectsPerWidget = DIVIDE(COUNT('Defects'[DefectID]), DISTINCTCOUNT('Defects'[WidgetID])).
Now suppose I wanted to get the DefectsPerWidget from just the last 30 days? How could I alter the DAX to do that? Ultimately I want to report on it by ProductName.
Similarly, is there a way to get the DefectsPerWidget of only the last 10 WidgetID's inspected? Problem is an inspection can occasionally span multiple days. If this were SQL, I would get the last 10 WidgetID's processed as SELECT WidgetID, MAX(InspectionDate) order by 2 desc.
Thank you!
Hi @bvy ,
You can modify your measure like
WidgetsPerDefect = CALCULATE(DIVIDE(COUNT('Defects'[DefectID]), DISTINCTCOUNT('Defects'[WidgetID])),FILTER('Defects',[InspectionDate]>=TODAY()-30))
If you want to filter WidgetID, you can create a filter and put in WidgetID.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@bvy Maybe:
DIVIDE(COUNTROWS(FILTER('Defects',[InspectionDate]>=TODAY()-30)), DISTINCTCOUNT('Defects'[WidgetID]))
@v-stephen-msft , @Greg_Deckler
Thank you both. This gets me close, but supposing I want a "categorical" 30 days? In other words, inspections aren't run on weekends and don't happen every single weekday. So supposing I want the last 30 days of just days that have activity? I feel like it might involve a table variable or some such...
Hi @bvy ,
Is the date in column InspectionDate an active date?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-stephen-msft Not sure what you're asking. It's a date field (no time) in the database. It doesn't change.
Hi @bvy ,
Sorry, let me elaborate.
How do you define the days that have activity? You said earlier that it’s not weekends, and it’s not necessarily all working days. How can this be reflected in the data model?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-stephen-msft Thanks for getting back to me. Suppose the ask was 5 days (just for this example). If this were SQL, I would get the "last 5 days" with select distinct top 5 InspectionDate from Defects order by InspectionDate desc
So if Defects has these records:
200 1 1/1/2021 Fading
201 1 1/1/2021 Peeling
202 1 1/3/2021 Cracking
203 1 1/4/2021 Peeling
204 1 1/7/2021 Peeling
205 1 1/7/2021 Peeling
206 1 1/7/2021 Chipping
207 1 1/7/2021 Chipping
208 1 1/10/2021 Peeling
209 1 1/11/2021 Fading
210 1 1/11/2021 Fading
211 1 1/15/2021 Peeling
"Last 5 days" would be:
1/15/2021
1/11/2021
1/10/2021
1/7/2021
1/4/2021
In this context, "last 5 days" is a bit of a misnomer, but the idea is that I want a metric around what happened over the most recent 5 (or 30) days that had activity.
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 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |