locked
ALL(DimensionTable) is cross applying everything RRS feed

  • Question

  • Hello,

    I'm pretty new to DAX and I'm having an issue which is think is pretty basic for anyone with some DAX knowledge...

    My model is pretty basic, I have a Fact table with 3 dimensions linked.  Only two tables are interesting right now for my problem and they are linked through a surrogate key:

    - FactTrip
    - DimStop

    In FactTrip, I have defined two measure:
    - Total In:=Sum('FactTrip'[PeopleIn])
    - Total Out:=Sum('FactTrip'[PeopleOut])

    Ok, quite easy so far, isn't?

    Now some business information.  I have many different trips that each have their own stop point.  I want to calculated what is the delta at the end of the trip.  It should be zero but for some reason, it is not always the case.  So I want this measure to be calculated for the whole trip and the result displayed with the same value if the user also drop the Stop #/Name on the pivot table.  So let's say that I have the following trip:

    Trip    Stop     PeopleIn     PeopleOut    ExpectionDelta
    A        A1         5                    0                    2 
    A        A2         0                    2                    2
    A        A3         1                    2                    2
    B        B1         5                    0                    4
    B        B2         1                    2                    4


    Now, I would like to create a Difference measure which would be something like:
    ExpectationDelta:=Calculate([Total In]-[Total Out],All('DimStop')

    It actually works, but the data on the pivot end up showing all the possible Stop for every trip just because I drop on the pivot this new measure..

    
    
    A        A1         5                    0                    2 
    A        A2         0                    2                    2
    A        A3         1                    2                    2
    A        B1                                                     2
    A        B2                                                     2
    B        B1         5                    0                    4
    B        B2         1                    2                    4
    B        A1                                                     4 
    B        A2                                                     4
    B        A3                                                     4
    


    What is it that I'm doing wrong?
    Thanks in advance for your help!

    • Edited by __Erik__ Thursday, July 7, 2016 4:57 PM Reformatting, grr....
    Thursday, July 7, 2016 4:50 PM

Answers

  • Your measure does exactly what you told it to do: return the delta, no matter what stop is selected in the current context. For instance, on your output row "Trip A, Stop B1" it calculates the delta for all stops on trip A, which equals 2 (even when B1 is not a part of A); hence this value is shown.

    One reason for this is that selecting a trip doesn't necessarily filter the stops. This might be solved by setting the crossfilter direction to 'both' on the relationship (this is only possible in Excel 2016 and the Power BI Desktop), although you should really take care doing this. Another approach could be to simply test on the output of other measures in this context, e.g.

    IF(NOT(ISBLANK([PeopleIn])) && NOT(ISBLANK([PeopleOut]));[ExpectationDelta])

    Saturday, July 9, 2016 5:19 AM
    Answerer

All replies

  • If you want the measure to only calculate for combinations which have a value in FactTrip then you can add that table in to your calculate expression. This should have the effect of only calculating for combinations of trip and stop that exist in FactTrip.

    ExpectationDelta:=Calculate([Total In]-[Total Out],All('DimStop'), FactTrip)


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

    Thursday, July 7, 2016 9:00 PM
  • Thanks for the reply.

    Unfortunately this doesn't work.  It surely removes the Stops that don't belongs to the trip but then the measure is not displaying the same value for every single stop.

    i.e.: it shows

    A / A1 / 5

    A / A2 / 3

    A / A3 / -1

    Rather than

    A / A1 / 2

    A / A2 / 2

    A / A3 / 2

    Friday, July 8, 2016 10:50 AM
  • Your measure does exactly what you told it to do: return the delta, no matter what stop is selected in the current context. For instance, on your output row "Trip A, Stop B1" it calculates the delta for all stops on trip A, which equals 2 (even when B1 is not a part of A); hence this value is shown.

    One reason for this is that selecting a trip doesn't necessarily filter the stops. This might be solved by setting the crossfilter direction to 'both' on the relationship (this is only possible in Excel 2016 and the Power BI Desktop), although you should really take care doing this. Another approach could be to simply test on the output of other measures in this context, e.g.

    IF(NOT(ISBLANK([PeopleIn])) && NOT(ISBLANK([PeopleOut]));[ExpectationDelta])

    Saturday, July 9, 2016 5:19 AM
    Answerer
  • Sorry, I was confusing this with a different scenario, adding the fact table just brings back in the context we were excluding with the ALL() function. I would just follow the simple route and use the expression suggested by Michiel.

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

    Saturday, July 9, 2016 7:11 AM