Why Two "Count Rentals" Measures Yield Different Answers?

[mgirvin]

Dear Team,

I have two Measures that I expect to yield the same answer and I am wondering if anyone can see why they are not yielding the same answer.

The goal of both formulas is to count the number of Rented Units based on a Start Date and Stop Date.

In the Data Model I have 4 tables:

1) fRentDateTransactions with columns for Start Date and Stop Date
2) dApartmentList with a unique list of apartments that is related to fRentDateTransactions
3) dCommunityAccount with a unique list of communities that is related to dApartmentList
4) dDate that is the Date table and is not related to any of the tables

I have a table with Year, Month and Day (from the dDate Table) on the rows and I have the two "count the number of Rented Units based on a Start Date and Stop Date" DAX Measures calculating for each Year, Month and Day.

The two formulas are:

# Rentals =
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE (
COUNTROWS (
FILTER (
fRentDateTransactions,
AND (
fRentDateTransactions[RentStartDate] <= MIN ( dDate[Date] ),
NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
)
&& OR (
fRentDateTransactions[RentStopDate] = MAX ( dDate[Date] ),
ISBLANK ( fRentDateTransactions[RentStopDate] )
)
)
)
)
)

and

# Rentals CALC =
VAR StartDate = MIN(dDate[Date])
VAR EndDate = MAX(dDate[Date])
VAR StartDateValidListTable =
FILTER(fRentDateTransactions,
AND(fRentDateTransactions[RentStartDate]<=StartDate,
NOT(ISBLANK(fRentDateTransactions[RentStartDate]))))
VAR EndDateValidListTable =
FILTER(fRentDateTransactions,
OR(fRentDateTransactions[RentStopDate]=EndDate,
ISBLANK(fRentDateTransactions[RentStopDate])))
RETURN
AVERAGEX (
VALUES ( dDate[Date] ),
CALCULATE(
COUNTROWS(fRentDateTransactions),
StartDateValidListTable,
EndDateValidListTable
)
)

Any ideas?

Also, anyone know how I can get DAX Measure to post with all the indents?

 

[gazpage]

In [1] the Filter Context From the row area of the report flows into VALUES( to define a list of days, which could be at the day, month or year) and then that flows into MIN and MAX. so MIN and MAX will pick out minimum and maximum dates from Filter Context From the row area of the report for each row in the report. That is, it will pick out the minimum and maximum dates from the Filter Context of the dDate Date Table. Note: there is no relationship between Date and Fact table.

This part isn't quite right. AVERAGEX is an iterator, so it goes through each row in the table in the first argument, calculates the value in the second, and averages the result.

The table in your case is a column of dates in the filter context. So the averagex iterates thought the table and then works out the min and max for that row, not for all rows.

 

[mgirvin]

I am 100% lost. I 100% do not understand. (Formula shown below).

Please help me to understand.

As I see it, for formula # 1: "AVERAGEX (VALUES ( dDate[Date] )," will see the Filter Context right away.

Then for formula #2 , VAR StartDate =
MIN ( dDate[Date] )
VAR EndDate =
MAX ( dDate[Date] )
will see the filter context right away too.

How are they not seeing the same thing?

#1 Rentals =
AVERAGEX (
    VALUES ( dDate[Date] ),
    CALCULATE (
        COUNTROWS (
            FILTER (
                fRentDateTransactions,
                AND (
                    fRentDateTransactions[RentStartDate] <= MIN ( dDate[Date] ),
                    NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
                )
                    && OR (
                        fRentDateTransactions[RentStopDate] = MAX ( dDate[Date] ),
                        ISBLANK ( fRentDateTransactions[RentStopDate] )
                    )
            )
        )
    )
)
 
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]  Rentals CALC =
VAR StartDate =
    MIN ( dDate[Date] )
VAR EndDate =
    MAX ( dDate[Date] )
VAR StartDateValidListTable =
    FILTER (
        fRentDateTransactions,
        AND (
            fRentDateTransactions[RentStartDate] <= StartDate,
            NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
        )
    )
VAR EndDateValidListTable =
    FILTER (
        fRentDateTransactions,
        OR (
            fRentDateTransactions[RentStopDate] = EndDate,
            ISBLANK ( fRentDateTransactions[RentStopDate] )
        )
    )
RETURN
    AVERAGEX (
        VALUES ( dDate[Date] ),
        CALCULATE (
            COUNTROWS ( fRentDateTransactions ),
            StartDateValidListTable,
            EndDateValidListTable
        )
    )

 

[gazpage]

Ok, sorry my prior explanation wasn't clear enough. I will try again with an example.

I think you don't understand how iterators (the ones of the format functionX) work. Here is the description of AVERAGEX https://msdn.microsoft.com/en-us/library/ee634546.aspx

Lets imagine a super simple table like this:

Date       Value
1/1/16    1
2/1/16    2

There would be all the other dates in this table, but out filter context only includes these two so we can ignore the rest.

And two measures like this.

Measure1:=
VAR MaxValue =
    MAX ( Table[Value] )

RETURN
    AVERAGEX (
        VALUES ( Table[Date] ),
        MaxValue
    )

Measure2:=
AVERAGEX (
        VALUES ( Table[Date] ),
        CALCULATE ( Max ( Table[Value] ) )
    )

Lets assume our filter context is for both these dates, writing this out the steps the measures will do the following:

Measure 1
1. Calculate the Variable MaxValue in the filter context. This is 2.
2. Iterate over VALUES ( Table[Date] ) and calculate MaxValue for each date. We already calculated MaxValue and it doesn't change, so the value for 1/1/16 is 2 and 2/1/16 is 2.
3. Take the average of these values. The average of 2 and 2 is 2.
4. Answer is 2.

Measure 2
1. Iterate over VALUES ( Table[Date] ) and calculate CALCULATE ( MAX ( Table[Value] ) ) for each date. For 1/1/16 this is 1, and for 2/1/16 this is 2.
2. Take the average of these values. The average of 1 and 2 is 1.5.
3. The answer is 1.5.

I hope this simple example helps. I am not sure I can break it down any further.

 

[mgirvin]

Thank you for your break down and clear explanation. When I try your example I get the same result as you describe. The formulas I created are here:

AverageOfMaxVAR = 
	VAR MaxValue =
    MAX ( 'Table'[Value] )
	RETURN
    AVERAGEX (
    	VALUES ( 'Table'[Date] ),
    	MaxValue
    )

and

AverageOfMaxNoVAR =
AVERAGEX (
	VALUES ( 'Table'[Date] ),
	CALCULATE ( MAX ( 'Table'[Value] ) )
)

But when I try the table setup as I described in post #1 , when there is a date table and no relationship between the date table and fact table and I have the Date Column from the Date Table in the Row Area of the report, I get the same answer from both formulas. When I create your small data set example but add a date table with no relationship with the fact table and use the Data Table column in the report and in the formula, I get an answer of 2 for each formula. Here are the formulas I used:

AverageOfMaxVAROnDateColumn = 
	VAR MaxValue =
    MAX ( 'Table'[Value] )
	RETURN
    AVERAGEX (
    	VALUES ( dDate[Date] ),
    	MaxValue
    )

and

AverageOfMaxNoVAROnDateColumn = AVERAGEX (
	VALUES ( dDate[Date] ),
	CALCULATE ( MAX ( 'Table'[Value] ) )
)

So it seems that all my confusion about getting the same answer for both formulas stems from the Date Table and no Relationship combined with the Data Column from the Date Table in both the report and formula.

Thank you very much, gazpage, for helping me to learn more about DAX : )