Hi, I have Issued so basically this is just sample data
Here I have to count which ones are the warning and non- warnings basically I have the granularity of data on a daily basis for 12 months so the first thing I do is create measurements to see which ones are categorized as warning and non-warning but when I tried to create measurement based on that IF function I can't get the total correct number of warning.
Here is the measurement I'm already done with:
*Metrics 1 are (Total of Fatal Incident + Non Fatal Incident) / Total Complete Order
First I create IF :
Category =
IF(
AND(
'Query1'[Metrics 1] > 'Parameter : Metrics 1'[Parameter Value],
sum('Query1'[Non Fatal Incident]) > 'Non Fatal'[Non Fatal Value] ||
sum('Query1'[Fatal Incident]) > 'Fatal'[Fatal Value]
), "Warning","Non")
By using this i can create summarize which ID that are warning and non-warning so next step i want to count how many distinc ID that are included in warning, so i use this measurement :
CALCULATE(
DISTINCTCOUNT('Query1'[ID]),
FILTER('Query1','Query1'[Category] ="Warning"))
However its turn out that there is a lot of ID that category as Warning are not counted by this measurement, any idea how to do it ? If i use column calculation i can get correct number but its turn out not dynamic and can't have date filter.
So I tried to use another way by using var but as far I see I can't create dynamic column measurement if I just want to filter it by 2 months or 3 months. Basically, the final result I want to see is how many distinct ID that is considered as warnings but should be able to dynamic filter by date because if I want it to be dynamic I need to use measurement, but if I do so it's not giving me a correct number, but if I used column its can't be dynamic because its counts all row
| Date | ID | Non Fatal Incident | Fatal Incident | Complete Order |
| 2023-01-01 | A-1 | 0 | 1 | 2000 |
| 2023-01-02 | A-1 | 1 | 0 | 1000 |
| 2023-01-03 | A-1 | 0 | 0 | 500 |
| 2023-01-04 | A-1 | 0 | 0 | 322 |
| 2023-01-05 | A-1 | 1 | 0 | 644 |
| 2023-01-06 | A-1 | 1 | 0 | 600 |
| 2023-01-07 | A-1 | 1 | 0 | 233 |
| 2023-01-08 | A-1 | 2 | 0 | 500 |
| 2023-01-09 | A-2 | 0 | 1 | 600 |
| 2023-01-10 | A-2 | 0 | 1 | 233 |
| 2023-01-11 | A-2 | 0 | 0 | 421 |
| 2023-01-12 | A-2 | 0 | 0 | 563 |
| 2023-01-13 | A-2 | 0 | 2 | 654 |
| 2023-01-14 | A-2 | 1 | 3 | 634 |
| 2023-01-15 | A-2 | 1 | 0 | 235 |
| 2023-01-16 | A-2 | 1 | 0 | 754 |
| 2023-01-17 | A-2 | 1 | 0 | 345 |
