Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working on creating injury logs. Here is the sample data:
Case | Type | Start Date | Days |
X | LD | 9/1/2017 | 100 |
X | LD | 12/1/2017 | 20 |
X | LT | 10/1/2017 | 40 |
X | LT | 11/1/2017 | 80 |
Y | LT | 10/1/2017 | 50 |
Y | LT | 9/1/2017 | 50 |
Y | LD | 10/1/2017 | 70 |
Y | LD | 11/1/2017 | 90 |
Y | LT | 12/1/2017 | 30 |
I need to aggregate the total periods for Cases X and Y based on the Type (LD or LT). The rules are:
Here is the results I am looking for:
LD | LT | |
X | 120 | 60 |
Y | 50 | 130 |
I appreciate your help!
Solved! Go to Solution.
Hi @mvrali,
It seems like you need to calculate based on specific sort order, I think you need to add index column to calculate loop table.
Steps:
1. Enter into query editor and add index column.
2. Add calculated column to change days based on rolling target.
Filtered Days = VAR _previous = SUMX ( FILTER ( Test, [Case] = EARLIER ( Test[Case] ) && [Index] < EARLIER ( Test[Index] ) ), [Days] ) VAR _current = SUMX ( FILTER ( Test, [Case] = EARLIER ( Test[Case] ) && [Index] <= EARLIER ( Test[Index] ) ), [Days] ) RETURN IF ( _current <= 180, [Days], IF ( _previous < 180 && _current > 180, 180 - _previous, 0 ) )
3. Create matrix visual with above columns.
Regards,
Xiaoxin Sheng
Hi @mvrali,
It seems like you need to calculate based on specific sort order, I think you need to add index column to calculate loop table.
Steps:
1. Enter into query editor and add index column.
2. Add calculated column to change days based on rolling target.
Filtered Days = VAR _previous = SUMX ( FILTER ( Test, [Case] = EARLIER ( Test[Case] ) && [Index] < EARLIER ( Test[Index] ) ), [Days] ) VAR _current = SUMX ( FILTER ( Test, [Case] = EARLIER ( Test[Case] ) && [Index] <= EARLIER ( Test[Index] ) ), [Days] ) RETURN IF ( _current <= 180, [Days], IF ( _previous < 180 && _current > 180, 180 - _previous, 0 ) )
3. Create matrix visual with above columns.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |