Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to create a measure based on 2 tables (fixed data and dataset). The measure is the Daily Availability (number and/or percentage).
Table 1 has all the different Max availability for each terminal:
Terminal | Max GA |
1 | 10 |
2 | 20 |
3 | 10 |
Table 2 has all the unique values of unavailability.
Date | Terminal |
15/08/2019 | 1 |
15/08/2019 | 3 |
15/08/2019 | 1 |
15/08/2019 | 3 |
16/08/2019 | 1 |
18/08/2019 | 1 |
Table 1 and 2 has a relationship based on terminal.
For the visual I am using my date table that has a relationship with table 2 based on date.
When I test the calculation per day, the expression I used does not hold true when there are multiple entries of a specific terminal on a specific day.
My expression: GA= sumx(Table2,related(Table1Column2)-[Unavailable])
Date | Terminal | Max | Unavailable | GA |
15/08/2019 | 1 | 10 | 3 | 7 |
15/08/2019 | 3 | 10 | 2 | 8 |
15/08/2019 | 1 | 10 | 1 | 9 |
15/08/2019 | 3 | 10 | 5 | 5 |
16/08/2019 | 1 | 10 | 3 | 7 |
18/08/2019 | 1 | 10 | 2 | 8 |
which is not true because its suppose to be:
Date | Terminal | Max | Unavailable | GA |
15/08/2019 | 1 | 10 | 3 | 7 |
15/08/2019 | 3 | 10 | 2 | 8 |
15/08/2019 | 1 | 10 | 1 | 6 |
15/08/2019 | 3 | 10 | 5 | 3 |
16/08/2019 | 1 | 10 | 3 | 7 |
18/08/2019 | 1 | 10 | 2 | 8 |
So that when I filter on date first, I see the final GA per terminal. Please help.
Solved! Go to Solution.
Hi @Noah_Mom ,
At first, you need to create an index column in the Table 2.
Then create “value” , “count” , ”UnavailableAgg” columns in order.
value = RANKX ( FILTER ( Table2, Table2[Terminal] = EARLIER ( Table2[Terminal] ) && Table2[Date] = EARLIER ( Table2[Date] ) ), Table2[Terminal], , )
Count = CALCULATE ( SUM ( 'Table2'[Value] ), FILTER ( ALLEXCEPT ( 'Table2', 'Table2'[Terminal] ), 'Table2'[Date] = EARLIER ( 'Table2'[Date] ) && 'Table2'[Terminal] = EARLIER ( 'Table2'[Terminal] ) && 'Table2'[index] <= EARLIER ( 'Table2'[index] ) ) )
UnavailableAgg = CALCULATE ( SUM ( Table2[Unavailable] ), FILTER ( Table2, Table2[Count] <= EARLIER ( Table2[Count] ) && Table2[Terminal] = EARLIER ( Table2[Terminal] ) && Table2[Date] = EARLIER ( Table2[Date] ) ) )
Now, modify your measure “GA” and get the right visual.
GA = SUMX ( Table2, RELATED ( Table1[Max GA] ) - Table2[UnavailableAgg] )
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Noah_Mom ,
At first, you need to create an index column in the Table 2.
Then create “value” , “count” , ”UnavailableAgg” columns in order.
value = RANKX ( FILTER ( Table2, Table2[Terminal] = EARLIER ( Table2[Terminal] ) && Table2[Date] = EARLIER ( Table2[Date] ) ), Table2[Terminal], , )
Count = CALCULATE ( SUM ( 'Table2'[Value] ), FILTER ( ALLEXCEPT ( 'Table2', 'Table2'[Terminal] ), 'Table2'[Date] = EARLIER ( 'Table2'[Date] ) && 'Table2'[Terminal] = EARLIER ( 'Table2'[Terminal] ) && 'Table2'[index] <= EARLIER ( 'Table2'[index] ) ) )
UnavailableAgg = CALCULATE ( SUM ( Table2[Unavailable] ), FILTER ( Table2, Table2[Count] <= EARLIER ( Table2[Count] ) && Table2[Terminal] = EARLIER ( Table2[Terminal] ) && Table2[Date] = EARLIER ( Table2[Date] ) ) )
Now, modify your measure “GA” and get the right visual.
GA = SUMX ( Table2, RELATED ( Table1[Max GA] ) - Table2[UnavailableAgg] )
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |