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.
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.
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |