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

Question

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 Answer

Hi Noah_Mom&nbsp;,
&nbsp;
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] )
            &amp;&amp; Table2[Date] = EARLIER ( Table2[Date] )
    ),
    Table2[Terminal],
    ,
)

Count =
CALCULATE (
    SUM ( 'Table2'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table2', 'Table2'[Terminal] ),
        'Table2'[Date] = EARLIER ( 'Table2'[Date] )
            &amp;&amp; 'Table2'[Terminal] = EARLIER ( 'Table2'[Terminal] )
            &amp;&amp; 'Table2'[index] &lt;= EARLIER ( 'Table2'[index] )
    )
)

UnavailableAgg =
CALCULATE (
    SUM ( Table2[Unavailable] ),
    FILTER (
        Table2,
        Table2[Count] &lt;= EARLIER ( Table2[Count] )
            &amp;&amp; Table2[Terminal] = EARLIER ( Table2[Terminal] )
            &amp;&amp; Table2[Date] = EARLIER ( Table2[Date] )
    )
)

Now, modify your measure “GA” and get the right visual.
GA =
SUMX ( Table2, RELATED ( Table1[Max GA] ) - Table2[UnavailableAgg] )

&nbsp;
Best Regards,
Eads
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

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

1 Reply

Related content

Need help creating a measure

Help to create DAX Measure

Create tooltip based measure

Create a Measure to Count based on a condition

Create a measure Based on Visual values