Efficient DAX measure - result per row

[Anonymous]

 Hi,

I am trying to write an efficient DAX measure, but it keeps giving me an error saying there is not enough memory.

I have 4 tables. The following 3 in the image, as well as a dates table, that has a row from the Min date to max date of the vehicle table.

I want to create a final table/chart that looks like the following, with the rule

For every single date between the vehicles valid from and to date,

What is its availablity? (if there is a maintenance date, then it is should be flagged as "maintenance", if there was a trip on that date, it should be "used" else "available" )

I also want to create a chart that counts the rows for each date to see how many Cars are being used, not used and maintained.

Here is my existing measure which is giving out of memory errors.

Use Measure =
Var var_maintenance = CALCULATE(
FIRSTNONBLANK('Maintenance Table'[Car],1),
FILTER('Maintenance Table',
'Maintenance Table'[Car] = RELATED(Vehicle Table[Car]) &&
'Maintenance Table'[Maintenance Date] = RELATED('Date'[Date])
)
)
return
SWITCH(TRUE(),
var_maintenance <> BLANK(), "Maintenance",
sum(Trips Table[Distance Travelled]) = BLANK(), "Not Used",
"Used"
)

[Ashish_Mathur]

Hi,

Share those 3 tables in a format that can be pasted in an MS Excel workbook.

[Anonymous]

Thanks @v-alq-msft . I had previously done this using Power Query - expanding the table. 

I was hoping to avoid that, as I end up with an extremely large table.

@Ashish_Mathur , here are the tables

Vehicle

Car	From	to
A	1/01/2020	10/01/2020
B	5/01/2020	12/01/2020
C	7/01/2020	14/01/2020

Mtc

Car	Maint Date
A	2/01/2020
A	3/01/2020
A	5/01/2020
B	6/01/2020
C	8/01/2020
C	10/01/2020

trips

Car	Date	Distance
A	1/01/2020	10.1
A	8/01/2020	15
B	7/01/2020	33.5
B	8/01/2020	36.2
B	9/01/2020	12.5
C	7/01/2020	19.8
C	9/01/2020	21.2
C	11/01/2020	12.3
C	13/01/2020	13.5

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur 

I was hoping to avoid creating an additional table.

I wanted to achieve the outcome with measures

3 measures - 1 each to determine "used" "not used" and "maintenance" in order to create a stacked column chart that shows these 3 counts per day

[Ashish_Mathur]

Hi,

Which additional table have i created.  Unique vehicles and Calendar tables are definitely required.  Only 1 measure has been used.

[Anonymous]

Hi

@Ashish_Mathur , I am referring to the expansion of the Vehicles table.

I have several thousand vehicles, and if I expand that table to have 1 row per date, I end up with a massive table.

I wanted to avoid this massive table, but if this is the only possible way or the best way, then that is the way I will do it.

Thanks again

[Ashish_Mathur]

Hi,

That is the only way I am aware of solving the question.  If my previous reply helped, please mark it as Answer.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Calendar(a calculated table):

Calendar = CALENDAR(MIN(Vehicle[From Date]),MAX(Vehicle[To Date]))

Table(a calculated table):

Table = 
var tab = 
UNION(
ADDCOLUMNS(
    GENERATESERIES(
        LOOKUPVALUE(Vehicle[From Date],Vehicle[Car],"A"),
        LOOKUPVALUE(Vehicle[To Date],Vehicle[Car],"A"),
        1
    ),
    "Car",
    "A"
),
ADDCOLUMNS(
    GENERATESERIES(
        LOOKUPVALUE(Vehicle[From Date],Vehicle[Car],"B"),
        LOOKUPVALUE(Vehicle[To Date],Vehicle[Car],"B"),
        1
    ),
    "Car",
    "B"
),
ADDCOLUMNS(
    GENERATESERIES(
        LOOKUPVALUE(Vehicle[From Date],Vehicle[Car],"C"),
        LOOKUPVALUE(Vehicle[To Date],Vehicle[Car],"C"),
        1
    ),
    "Car",
    "C"
)
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Usage",
    IF(
        COUNTROWS(
            FILTER(
                Maintenance,
                [Car]=EARLIER([Car])&&
                [Maintenance Date]=EARLIER([Value])
            )
        )>0,
        "Maintenance",
        IF(
            COUNTROWS(
                FILTER(
                    Trips,
                    [Car]=EARLIER([Car])&&
                    [Date]=EARLIER([Value])
                )
            )>0,
            "Used",
            "Available"
        )
    )
)
var t =
ADDCOLUMNS(
    newtab,
    "Distance",
    IF(
        [Usage]="Used",
        LOOKUPVALUE(Trips[Distance Travelled],Trips[Car],[Car],Trips[Date],[Value])
    )
)
return
t

There is a relationship between 'Calendar' and 'Table'. You may create a measue as below.

Result = COUNTROWS('Table')

Result: 

   Table:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a date table and Car. Refer to file how to create using distinct. If needed

The vehicle you do not join with Date. Other two join. Join all three with Car.

Current = CALCULATE(COUNTx(FILTER(Table,Table[From Date]<=max('Date'[Date]) && ( Table[To Date]>max('Date'[Date]))),(Table[Car])))

Create dimension - https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

[Anonymous]

Thank you.

I have written a measure to count used and count maintenance but I am having trouble writing one to count for each date, if a vehicle is NOT being used.

Here are my measure which are working.

Can you help create one which counts per date how many Cars are not being used (no trip or maintenance for that date on that car)

countmtc =

CALCULATE(COUNTX(

FILTER(Mtc, Mtc[Maint Date] = RELATED('Date'[Date])),Mtc[Maint Date]

))

countused =

CALCULATE(COUNTX(

FILTER(trips, trips[Date] = RELATED('Date'[Date])),trips[Date]

)

)

Here is my model