locked
Why Two "Count Rentals" Measures Yield Different Answers? RRS feed

  • Question

  • 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?


    Sincerely, Mike Girvin

    Saturday, September 16, 2017 5:18 PM

Answers

  • Doing the context transition of a calculate inside an iterative function like averagex is likely to cause performance issues. Have you tried something like the following using earlier to get the date value from the averagex iteration?

    AVERAGEX (
        VALUES ( dDate[Date] ),
        COUNTROWS (
            FILTER (
                fRentDateTransactions,
                (
                    fRentDateTransactions[RentStartDate] <= EARLIER( dDate[Date] )
                    && NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
                )
                &&  
                (
                    fRentDateTransactions[RentStopDate] = EARLIER( dDate[Date] )
                    || ISBLANK ( fRentDateTransactions[RentStopDate] )
                )
            )
        )
    )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, September 26, 2017 1:30 AM

All replies

  • Anyone know how I can get the DAX Measure to have indents when I post at this site?

    Sincerely, Mike Girvin

    Saturday, September 16, 2017 5:18 PM
  • This is because the VARs are evaluated in a different context than their counterparts in the first formula.

    CALCULATE transforms row context into filter context. Check out this article and you will see that "hitting the CALCULATE wall" seems to belong to many DAX-journeys: Hitting the CALCULATE wall

    And why variables are so helpful: https://www.sqlbi.com/articles/variables-in-dax/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!



    Sunday, September 17, 2017 7:12 AM
    Answerer
  • Thank you for your reply, Imke!!

    When I look at the two formulas, I see that the Filter Context From the row area of the report flow into both formulas and yield the same numbers for the VAR. I see it this way:

    Formula # [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] )
    )
    )
    )
    )
    )

    Formula #[2]:
    # 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
    )
    )

    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.

    In [2] the VARs are up front and so they see the Filter Context From the row area of the report right away and should calculate the same dates as [1].

    Am I way off base in my thinking?


    Sincerely, Mike Girvin

    Sunday, September 17, 2017 2:58 PM
  • Also, when I move the VARS into AVERAGEX, I get the same answers. These two formulas yield the same answer:

    # [1]:
    AVERAGEX
    (
    VALUES(dDate[Date]),
    VAR StartDate = MIN(dDate[Date])
    VAR EndDate = MAX (dDate[Date])
    VAR RentDatesBeforeMin = 
    FILTER(
                    fRentDates,
    AND(
                        fRentDates[RentStartDate] <= StartDate,
    NOT (ISBLANK(fRentDates[RentStartDate]))
                    )
                )
    VAR RentDatesEqualMax =
    FILTER(
                    fRentDates,
    OR(
                        fRentDates[RentStopDate] = MAX(dDate[Date]),
    ISBLANK(fRentDates[RentStopDate])
                      )
                )
    RETURN
    CALCULATE(
    COUNTROWS(fRentDates),
                RentDatesBeforeMin,
                RentDatesEqualMax,
    ALL(dDate)
            )
        )

    # [2]:
    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
    )
    )                                                                                                                                                                                                  


    Sincerely, Mike Girvin

    Sunday, September 17, 2017 3:11 PM
  • My real goal is trying to speed up Formula # [1]. [1] yields that correct answer. [2] yields the wrong answer. The two formulas are below. When I use DAX Studio, [2] calculates many, many times faster than [1]. But, until I can get [2] to yield the same answer, it is a mute point. Ultimately, all I want is to take [1] and change it so it calculates more quickly. Any ideas?

    # [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] )
    )
    )
    )
    )
    )

    and

    # [2]:
    # 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
    )
    )


    Sincerely, Mike Girvin


    Sunday, September 17, 2017 3:14 PM
  • Hi Mike:

    Thanks for your question.

    Anyone know how I can get the DAX Measure to have indents when I post at this site?
    please copy you DAX to below site,DAX Formatter is a free tool by SQLBI that transform your raw DAX formulas into clean, beautiful and readable code. 
    http://www.daxformatter.com/

    Based on the logic provided by you, please try below DAX, see if it works for you:

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


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 18, 2017 7:41 AM
  • The formula does not yield the correct answer because there are blanks in Start Date column, but thank you for your help.

    Sincerely, Mike Girvin

    Sunday, September 24, 2017 1:26 PM
  • Doing the context transition of a calculate inside an iterative function like averagex is likely to cause performance issues. Have you tried something like the following using earlier to get the date value from the averagex iteration?

    AVERAGEX (
        VALUES ( dDate[Date] ),
        COUNTROWS (
            FILTER (
                fRentDateTransactions,
                (
                    fRentDateTransactions[RentStartDate] <= EARLIER( dDate[Date] )
                    && NOT ( ISBLANK ( fRentDateTransactions[RentStartDate] ) )
                )
                &&  
                (
                    fRentDateTransactions[RentStopDate] = EARLIER( dDate[Date] )
                    || ISBLANK ( fRentDateTransactions[RentStopDate] )
                )
            )
        )
    )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, September 26, 2017 1:30 AM