none
DAX measure in PowerPivot has a different behaviour againt the corresponding DAX query

    Question

  • Hi,

    in a workbook I've implemented a tabular model with a fact table and two times table, StartTimes and EndTimes, related with the fact table. Moreover, I've created a Dates table, disconnected, in order to simulate the parameters passing for a SSRS report.

    I'm trying this measure:

    SumX (Values ( Fact_Collegamenti_2[ID_Utente]);
      CountRows (Filter (Values ( Fact_Collegamenti_2[ID_BTS] );
          Calculate ( Max ( Fact_Collegamenti_2[ID] ) ) =
    			Calculate (Max ( Fact_Collegamenti_2[ID] );
    				FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data]));
    				All ( Fact_Collegamenti_2[ID_BTS] ) )
        )
      )
    )

    that doesn't calculate any values for row label 1:

    The workbook is at this link: https://skydrive.live.com/?cid=c0d76223f528eb50&id=C0D76223F528EB50%21159&authkey=!AK9yvvLLe5OLhbQ#!/edit.aspx?cid=C0D76223F528EB50&resid=C0D76223F528EB50%21163&app=Excel&authkey=%21AK9yvvLLe5OLhbQ

    But when I run this dax query:

    evaluate 
    FILTER
    (
    addcolumns 
    ( 'Fact_Collegamenti_2',
    "MaxID",
    Calculate (Max ( Fact_Collegamenti_2[ID] ),
    	FILTER(StartTimes, StartTimes[Data] <= datevalue("03/01/2013")),
    All ( Fact_Collegamenti_2[ID_BTS] ) ),
    "Conta_Connessioni",
     CountRows (Filter (Values ( Fact_Collegamenti_2[ID_BTS] ),
          Calculate ( Max ( Fact_Collegamenti_2[ID] ) ) =
    			Calculate (Max ( Fact_Collegamenti_2[ID] ),
    				FILTER(StartTimes, StartTimes[Data] <= datevalue("03/01/2013")),
    				All ( Fact_Collegamenti_2[ID_BTS] ) )
        )
      )
    ),
    [MaxID] > 0
    )
    order by 'Fact_Collegamenti_2'[ID_Utente], 'Fact_Collegamenti_2'[ID] desc

    I can see the fact row with ID = 7:

    Any suggests to me, in order to solve this strange issue? Thanks

    • Edited by pscorca Monday, February 04, 2013 6:28 PM
    Monday, February 04, 2013 6:18 PM

Answers

  • The formula did not use any MAX function because I understood from your previous post that the measure should return the same result whatever the selected BTS. This was apparently a misunderstanding.

    Judging by your opening post, you are very capable of writing sophisticated DAX functions. The only reason why you did not succeed in writing a measure that returns the results you expect is because you have difficulties stating what your expectations are. (This is the same for everybody, not just you.)

    That is why I provided a table (copy below), including a few combinations taken from your data, with sub-totals and totals. This kind of exercise really helps phrasing requirements. Note that the key point is to specify what the returning values should be for different tupls but also for totals and subtotals.

    Give it a try. In the process, you might find a way to write the measure all by yourself.

    Filter Dates[Data] = "03/01/2013"

    ID_Utente ID_BTS ID Expected result
    1 1 2 ?
    1 1 5 ?
    1 1 7 ?
    1 1 All ?
    1 2 All ?
    1 All All ?
    3 2 6 ?
    3 2 8 ?
    3 2 12 ?
    3 2 All ?
    3 4 All ?
    3 6 All ?
    3 All All ?
    All All All ?

    The Data Specialist (Blog)

    Tuesday, February 19, 2013 1:03 PM
  • PowerPivot and Tabular are similar to MD. The result of complex MDX expressions may also depend on how the query has been filtered. In fact, MDX tends to be more complex than equivalent DAX expressions.

    As I said before, writing a measure that gives you the expected results requires you to precisely define what the expected results should be for different selections.


    The Data Specialist (Blog)

    Tuesday, March 12, 2013 11:12 AM

All replies

  • When you define a measure, a CALCULATE expression is implicitly added around the expression.

    That is SUMX( ...) will become CALCULATE(SUMX(...)).

    This does not happen within an ADDCOLUMNS expression.

    My guess is that, in your measure, VALUES( Fact_Collegamenti_2[ID_BTS] ) in the COUNTROWS expression refers to the current filter context (from the query), not to the row context you provided in your SUMX expression.

    In your query however, since there is no CALCULATE, VALUES( Fact_Collegamenti_2[ID_BTS] ) will most probably refer to all values in Fact_Collegamenti_2[ID_BTS].

    In turn this will impact the result of CALCULATE( MAX ( Fact_Collegamenti_2[ID] ) ).

    If you wish to achieve the same result in your query as in your pivot table, then wrap the COUNTROWS(FILTER(...))) expression with a CALCULATE expression.

    Let us know if that works for you.


    The Data Specialist (Blog)

    Wednesday, February 06, 2013 12:21 PM
  • Hi Laurent, thanks for your reply not really more clear to me.

    I'm trying to implement the measure above reported in PowerPivot. In order to test it, I've written the DAX query. As I've written, there are different behaviours, but I'm interesting to correct the measure in PowerPivot because loses the connection with ID = 7 that I can see with DAX query.

    In the query I haven't added the piece

    SumX (Values ( Fact_Collegamenti_2[ID_Utente]); ...

    because I'm using the query to verify the DAX measure.

    Thanks

    Wednesday, February 06, 2013 5:35 PM
  • Maybe I could be more specific, if you could explain briefly what you are trying to achieve and what [ID], [ID_BTS], and [ID_Utente] are.


    The Data Specialist (Blog)

    Thursday, February 07, 2013 10:14 AM
  • Ok, the ID is the key of my fact table that could be useful to detect the last user connection instead to consider StartTime and StopTime registered in the fact table. StartTime and StopTime have date and time. The BTS is the tool or device that allows to an user to connect on the web.

    My fact table contains the records about the user connection, with a start time, an end time and the device used. I'm trying to get the counting the last user connection respect to a temporal interval, without losing the info about the BTS.

    Thanks

    Thursday, February 07, 2013 2:02 PM
  • When you refer to the column Fact_Collegamenti_2[ID] in your FILTER expression, this column has already been filtered within the current context. In your query, due to the CALCULATE function, it only contains the value of the [ID] attribute for the current row. Since this column identifies the row, the ALL( Fact_Collegamenti_2[ID_BTS]) part will have no impact on the result. The filter expression will be true for all connections that started on or before the 3rd of January.

    You will get the same results in your pivot table, if you drag the field Fact_Collegamenti_2[ID] on rows.

    Just to see what happens when the ID is not in the pivot table, just add the field [ID_BTS] instead and add a regular Max for [ID] to your pivot:

    [MaxID_Simple] := Calculate (Max ( Fact_Collegamenti_2[ID] ))

    You will see that the last connection for User 1 was the connnection with ID 25. So his last connection was after the 3rd of January. Same thing for the subtotal (Connection ID was 26). In that case, your filter condition is no longer true.


    The Data Specialist (Blog)


    Friday, February 08, 2013 11:37 AM
  • Hi Laurent, sorry but your reply isn't not really clear for me.

    I focus on the measure on PowerPivot. The DAX query is to test it.

    The measure is:

    SumX (Values ( Fact_Collegamenti_2[ID_Utente]);
      CountRows (Filter (Values ( Fact_Collegamenti_2[ID_BTS] );
          Calculate ( Max ( Fact_Collegamenti_2[ID] ) ) =
    			Calculate (Max ( Fact_Collegamenti_2[ID] );
    				FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data]));
    				All ( Fact_Collegamenti_2[ID_BTS] ) )
        )
      )
    )

    With this formula I want to count the last user connections in a temporal interval and sum them. With the last user connection the measure gets the device for the connection. An user could use more of one BST to connect.

    With the first step:

    Calculate (Max ( Fact_Collegamenti_2[ID] );
    	FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data])); All ( Fact_Collegamenti_2[ID_BTS] ) )

    I calculate the max id of the facts regardless the BST and depending on StartTime.

    Then, with

    Filter (
    Values ( Fact_Collegamenti_2[ID_BTS] );
          Calculate ( Max ( Fact_Collegamenti_2[ID] ) ) =
    			Calculate (Max ( Fact_Collegamenti_2[ID] );
    				FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data]));
    				All ( Fact_Collegamenti_2[ID_BTS] ) )
        )

    I want to filter the fact table respect to distinct values of BTS where the fact id is equals to the max id.

    Then count the filtered rows and so on.

    But this measure isn't really very right, because for user 1 no connections are counted.

    Thanks

    Friday, February 08, 2013 3:15 PM
  • Your measure behaves the same way in your query and your pivot table. However, it does not behave as you expected at different levels of aggregation.

    Let us assume your pivot table has the following filters:

    • [ID_Utente] = 1
    • [ID] = 7
    • Dates[Data] = 03/01/2013

    At that point, these filters already apply to your Fact_Collegamenti_2 table. This means that Calculate ( Max ( Fact_Collegamenti_2[ID] ) ) and Calculate (Max ( Fact_Collegamenti_2[ID] ); FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data]));    All ( Fact_Collegamenti_2[ID_BTS] ) ) are evaluated for that one row where [ID] =7. The result in both cases is 7, and the comparison returns True.

    However, if you only filter on

    • [ID_Utente] = 1
    • Dates[Data] = 03/01/2013

    both expressions will be evaluated for all connections that user 1 ever made. This means that Calculate ( Max ( Fact_Collegamenti_2[ID] ) )  will return the last connection ever made by user 1 (in your sample data, connection 26). However, Calculate (Max ( Fact_Collegamenti_2[ID] ); FILTER(StartTimes; StartTimes[Data] <= VALUES(Dates[Data]));    All ( Fact_Collegamenti_2[ID_BTS] ) ) returns the last connection prior to Dates[Data]. Since connection 26 took place after the 3rd of January, the condition evaluates to False and your filter expression does not return any values.

    I would like to suggest a way to fix this, but it is not 100% clear to me, what your final intent is. Amongst others, this could be:

    • Count the number of users whose last connection happened on or before the selected date.
    • Count the number of users who connected on or before the selected date. The measure behaviour should be based on the last connection within that interval.
    • Count the number of devices used - regardless of selected date - by those users who connected prior to the selected date.

    What results do you expect, when your pivot table looks like this?

    Filter Dates[Data] = "03/01/2013"

    ID_Utente ID_BTS ID Expected result
    1 1 2 ?
    1 1 5 ?
    1 1 7 ?
    1 1 All ?
    1 2 All ?
    1 All All ?
    3 2 6 ?
    3 2 8 ?
    3 2 12 ?
    3 2 All ?
    3 4 All ?
    3 6 All ?
    3 All All ?
    All All All ?
     

    The Data Specialist (Blog)

    Saturday, February 09, 2013 11:51 AM
  • Hi Laurent, thanks for your reply.

    However, it isn't really very clear for me because the measure doesn't function ONLY for the ID_UTENTE (or user id) = 1 and Dates[Data] = 03/01/2013. A measure or functions always or functions never.

    The goal of my PowerPivot measure is to count the last user connection happened on or before the selected date (03/01/2013). The aggregation is for user and bts or for only bts.

    If I returns to your table,

    for ID_Utente = 1 ... ID_BTS = 1 ... ID = 7 ... expected result for measure = 1

    for ID_Utente = 2 ... ID_BTS = nothing ... ID = nothing ... expected result for measure = nothing or zero

    for ID_Utente = 3 ... ID_BTS = 2 ... ID = 12 ... expected result for measure = 1

    for ID_Utente = 4 ... ID_BTS = 3 ... ID = 11 ... expected result for measure = 1

    for ID_Utente = 5 ... ID_BTS = 3 ... ID = 10 ... expected result for measure = 1

    and so on.

    Each last user connection must be counted one time on or before the selected date.

    Thanks

    Tuesday, February 12, 2013 10:42 AM
  • You are right in saying that a measure is either correct for all data or never.

    As I understand it from your description above, a user should be counted once, and only once, if he has had one connection - whatever the BTS - on or before the selected date.

    If I understood correctly, you might try something like this:

    [Measure] : =CALCULATE( COUNTROWS( VALUES('Utenti_2') )
                          ; CALCULATETABLE( 'Fact_Collegamenti_2'
                                ; FILTER( ALL('StartTimes')
                                    ; 'StartTimes'[Data] <= LASTDATE('Dates'[Data])
                                  )
                                ; ALL('BTS')
                  )
     )


    The Data Specialist (Blog)

    Tuesday, February 12, 2013 9:01 PM
  • Hi Laurent, thanks for your reply.

    Your formula becomes:

    CALCULATE(
              COUNTROWS( VALUES(Fact_Collegamenti_2[ID_Utente]) )
                           ; CALCULATETABLE( Fact_Collegamenti_2;
                              FILTER( ALL(StartTimes); StartTimes[Data] <= LASTDATE(Dates[Data])
                                   )
                                 ; ALL(Fact_Collegamenti_2[ID_BTS])
                   )
    )

    that it seems to function

    but not when I select also ID_BTS as a row filter, other ID_Utente.

    Fe, for Fact_Collegamenti_2[ID] = 7 it must be counted only the BTS with ID = 1.

    Moreover, I don't understand because you don't use the Max ( Fact_Collegamenti_2[ID] ). The ID of the fact table is useful to manage connection time with date and time.

    I think that the formula should function also if as row filter is present only ID_Utente, or only ID_BTS or both.

    Thanks


    • Edited by pscorca Saturday, February 16, 2013 7:29 AM adding
    Saturday, February 16, 2013 7:22 AM
  • The formula did not use any MAX function because I understood from your previous post that the measure should return the same result whatever the selected BTS. This was apparently a misunderstanding.

    Judging by your opening post, you are very capable of writing sophisticated DAX functions. The only reason why you did not succeed in writing a measure that returns the results you expect is because you have difficulties stating what your expectations are. (This is the same for everybody, not just you.)

    That is why I provided a table (copy below), including a few combinations taken from your data, with sub-totals and totals. This kind of exercise really helps phrasing requirements. Note that the key point is to specify what the returning values should be for different tupls but also for totals and subtotals.

    Give it a try. In the process, you might find a way to write the measure all by yourself.

    Filter Dates[Data] = "03/01/2013"

    ID_Utente ID_BTS ID Expected result
    1 1 2 ?
    1 1 5 ?
    1 1 7 ?
    1 1 All ?
    1 2 All ?
    1 All All ?
    3 2 6 ?
    3 2 8 ?
    3 2 12 ?
    3 2 All ?
    3 4 All ?
    3 6 All ?
    3 All All ?
    All All All ?

    The Data Specialist (Blog)

    Tuesday, February 19, 2013 1:03 PM
  • Hi Laurent, really I've not more experience with PowerPivot that I'm using from few months.

    It seems thant when I write a bit more complex DAX formula, the related measure value is closely linked to a filter/slices, that is I need to change/rewrite the DAX formula if I need to use a different filter. Generally, when I use a classic multidimensional cube I can slice and dice as I want without changing/rewriting any measures. So I've some doubts about the real effectiveness of PowerPivot, at least in this scenario. The DAX measure should depend on the underlying data model and less depend to the filters in a pivot table. Moreover, over PowerPivot it is available SSAS Tabular with the scope to represent an alternative respect to the multidimensional cube; so I expect that a PowerPivot or a Tabular model are logically similar to a multidimensional model.

    Sunday, March 10, 2013 6:23 AM
  • PowerPivot and Tabular are similar to MD. The result of complex MDX expressions may also depend on how the query has been filtered. In fact, MDX tends to be more complex than equivalent DAX expressions.

    As I said before, writing a measure that gives you the expected results requires you to precisely define what the expected results should be for different selections.


    The Data Specialist (Blog)

    Tuesday, March 12, 2013 11:12 AM