Creating a measure based on a fixed table and a fact table

[Noah_Mom]

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.

[v-eachen-msft · Accepted 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.