locked
Crossjoin measure with time intelligence fail RRS feed

  • Question

  • I am having trouble with a local PowerBI model that I am trying to build first in Power BI and then later to promote into SSAS Tabular.

    The requirement is to produce a Waterfall chart as follows (numbers instead of blocks, but you get the idea)


    Euros

    100,000

    2,000

    1,000

    5,000

    -5,000

    3,000

    5,000

    111,000

    Remerch Category Name

    Last Year

    Not Trading

    Exiting

    Same Brand Same Unit

    Remerchandising

    New

    Relocations

    Total


    Background


    • We have a simple fact table with sales per day per brand. Key Fields

    Trade Date

    Lease Code

    Sales

    Remerch Category

    • We calculate the"Remerch category" for each sale based on lease dates. This is the same for each Lease for each calendar year. We therefore can calculatethe Remerch category for last year (that may be a different). We also add LY Sales measure as simple "calculate..sameperiodlastyear".
    • For the waterfall, LY amount should be *subtracted* from the corresponding LY category and TY amount should be *added* to TY category.

    My approach

    • As the waterfall requires all columns to be shown, to be held in a single dimension attribute I have created a static table with the required "Remerch Category" columns in as shown in the above table
    • As no fact entry will ever have the "Last Year" value (as this is required just for the waterfall visual), I have assumed the only way to achieve this is with a cross join in the measure
    • I have a third table that I've created with "LY Total", "LY" and "TY" rows to allow logic to do the adding and subtracting to/from the appropriate categories

    The measure I'm having trouble with is

    Remerch Sales Value3 =
    SUMX (
        CROSSJOIN ( 'CubeData Final', 'Remerch  Category', CrossJoiner ),
        SWITCH (
            TRUE (),
            'Remerch  Category'[Remerch Category Id] = 1
                && CrossJoiner[Type] = "LY Total", CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( Dates[Calendar Date] ) ),
            'Remerch  Category'[Remerch Category Id] > 1
                && CrossJoiner[Type] = "TY"
                && 'Remerch  Category'[Remerch Category Id] = 'CubeData Final'[Remerch Category Id], [Sales],
            'Remerch  Category'[Remerch Category Id] > 1
                && CrossJoiner[Type] = "LY"
                && 'Remerch  Category'[Remerch Category Id]
                = 'CubeData Final'[Remerch Category LY Id], -1 * CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( Dates[Calendar Date] ) )
        )
    )

    The problem:

    • The problem I have is that my "Sales LY" measure that uses SamePeriodLastYear is always returning blank when it goes through the cross join.
    • It works fine just showing on the table as you can see on "Debug Remerch Sales" Page.
    • The "Sales" measure is working ok, so filter context is intact, so I guess this points to a problem with the time intelligence functions but I don't know how I can provide the necessary connection for my new cross join table through to the date table.
    • The logic is working ok, if I replace the [Sales LY] with a literal 1, you will see the correct values coming through on the debug table

    I
    think there must be a way to rewrite the Sales LY measure .. Maybe variables would help but I haven't figured out how yet… but I realise I'm at the point where I could really use a fresh pair of eyes and some experience!

    Pbix is here

    Thanks!


    Thursday, May 19, 2016 5:02 PM

Answers

  • Hi Brett,

    I haven't gone through your whole model, but first I would simplify your approach by eliminating the CROSSJOIN. This function creates a table with all combinations of rows in the joined tables, and it forces you to perform complex comparisons to do what you want.

    If you extend your 'Remerch Category' table with a Code column contains numbers, and use powers of 2 as values (1, 2, 4, 8 etc.) you can simply use a switch on the selected category:

    SWITCH(SUM('Remerch Category'[Code]), ...

    Then, for each category you can insert the exact calculation you need for that category:

    Remerch Sales Value:= SWITCH(SUM('Remerch Category'[Code]), 1, [Sales LY], 2, [Sales Not Trading], 4, ... (etc.)

    It looks like your Remerch Category is also in the CubeData Final table, and if the [Sales] measure works on that table and you have a relationship, you could simply move all 'standard' categories to the last ('Else') part of the SWITCH, e.g.

    Remerch Sales Value:= SWITCH(SUM('Remerch Category'[Code]), 1, [Sales LY], [Sales])

    This approach eliminates the use of CROSSJOIN and of SUMX.

    Wednesday, May 25, 2016 8:36 AM
    Answerer
  • I use powers of 2 to be sure I always land at the right branch in SWITCH. Suppose you code the categories with 1, 2, 3, 4; and put these in a slicer. When the user would select category 1 and 2, the sum would be 3 and you would end up returning the result for category 3.

    And you don't have to create a separate measure for each option, each option you can compute in a standard way can be handled by the 'else' clause I mentioned earlier. This would also automatically handle new categories that work the same way.

    BTW, it looks like you switched the lease type IDs for Y and Z (they differ between 2014 and 2015)?

    When using the relationship, my guess is you need to compute CALCULATE([Sales LY], ALL(LeaseType)) to get the last year row.

    Tuesday, May 31, 2016 7:32 AM
    Answerer

All replies

  • Hi Brett,

    I haven't gone through your whole model, but first I would simplify your approach by eliminating the CROSSJOIN. This function creates a table with all combinations of rows in the joined tables, and it forces you to perform complex comparisons to do what you want.

    If you extend your 'Remerch Category' table with a Code column contains numbers, and use powers of 2 as values (1, 2, 4, 8 etc.) you can simply use a switch on the selected category:

    SWITCH(SUM('Remerch Category'[Code]), ...

    Then, for each category you can insert the exact calculation you need for that category:

    Remerch Sales Value:= SWITCH(SUM('Remerch Category'[Code]), 1, [Sales LY], 2, [Sales Not Trading], 4, ... (etc.)

    It looks like your Remerch Category is also in the CubeData Final table, and if the [Sales] measure works on that table and you have a relationship, you could simply move all 'standard' categories to the last ('Else') part of the SWITCH, e.g.

    Remerch Sales Value:= SWITCH(SUM('Remerch Category'[Code]), 1, [Sales LY], [Sales])

    This approach eliminates the use of CROSSJOIN and of SUMX.

    Wednesday, May 25, 2016 8:36 AM
    Answerer
  • Thanks for the response Michiel!<o:p></o:p>

    I'm sorry but I'm not sure if I follow your proposed solution fully.

    I don't understand the benefit of using bitfields (1,2,4,8 etc) rather than just the sequential ID that I have now (1,2,3,4). It doesn't look like you are ever applying bitwise logic in your example?

    I understand that I could use the choice method without the cross join if I effectively built 12 measures, one for each of the 6 categories and one each for LY*-1 and one for TY. This feels slightly clumsy to me as I'd like it to be driven by a pattern described by the table rather than having to handcraft 2 measures for each row in my 'Remerch Category' table. I can avoid this with a copy & paste measure as below.. but still seems like it could be improved.

    I'd like to be able to add new rows in the future and it all be automatic if possible. As an educational case, is this possible in DAX?

    <o:p>I didn't see any way to add the relationship from sales['Remerch Category Id'] to 'Remerch Category'[Id] as then I cannot get the Last Year value as none of the sales rows have this value.  </o:p>

    Lets take a simplified model to help illustrate what I found:

    Sales table:

    Date, Lease, LeaseTypeId, Amount

    1/1/14, X, 2, 100

    1/1/14, Y, 2, 100

    1/1/14, Z, 3, 100

    1/1/15, X, 2, 200

    1/1/15, Y, 3, 200

    1/1/15, Z, 2, 200

    LeaseType Table:

    Id, Name

    1, Last Year

    2, A

    3, B

    I need a results table that, if filtered for year 2015, says

    Lease Type, Value

    Last Year, 300

    A, 200 (this is 200+200-100-100)

    B, 100 (this is 200-100)

    (Automatic total = 600 which is also TY total sales)

    If I leave the relationship off of Sales[LeaseTypeId] - LeaseType[Id] I can do this

    Remerch Sales Value = switch(sum['LeaseType'[LeaseType]),1,[Sales LY]

    ,2, calculate([Sales],Sales[LeaseTypeId]=2) + calculate([Sales]*-1,Sales[LeaseTypeId]=2,sameperiodlastyear(Dates[Date]))

    ,3, calculate([Sales],Sales[LeaseTypeId]=3) + calculate([Sales]*-1,Sales[LeaseTypeId]=3,sameperiodlastyear(Dates[Date]))

    etc..

    If I put the relationship on Sales[LeaseTypeId] - LeaseType[Id] and use

    Remerch Sales Value = switch(sum('LeaseType'[LeaseType]),1,[Sales LY],[Sales]+([Sales LY]*-1))

    then I get no Last Year row in my results, which I need.

    Thanks again for the help!

    Monday, May 30, 2016 2:25 PM
  • I use powers of 2 to be sure I always land at the right branch in SWITCH. Suppose you code the categories with 1, 2, 3, 4; and put these in a slicer. When the user would select category 1 and 2, the sum would be 3 and you would end up returning the result for category 3.

    And you don't have to create a separate measure for each option, each option you can compute in a standard way can be handled by the 'else' clause I mentioned earlier. This would also automatically handle new categories that work the same way.

    BTW, it looks like you switched the lease type IDs for Y and Z (they differ between 2014 and 2015)?

    When using the relationship, my guess is you need to compute CALCULATE([Sales LY], ALL(LeaseType)) to get the last year row.

    Tuesday, May 31, 2016 7:32 AM
    Answerer
  • Ok, I understand the bitwise approach now. It didn't relate to solving my actual issue, but I get the benefit.

    The switching of lease type is absolutely expected and this is one of the problems that perhaps you hadn't understood. It is expected that for different years, the same lease would be in a different category. It is summing by that category that is the challenge. 

    When I used the else statement as you wrote it and no relationship, I just got the same number for each category.

    When I used the else statement with the relationship, the figure wasn't right and I could never get the LeaseType 1 value (as none of the rows had this).

    I'll try again to use the calculate filter context change you mentioned and report back

    Thanks again for your help!

    Brett

    Thursday, June 2, 2016 1:11 PM
  • Just finally to say thanks Michel! This did work... (once I finally found the time to revisit this)

    My final formula was, as you intimated:

    Lease Type Sales = switch(sum('Lease Type'[Lease Type Id]),1, calculate([Sales LY], all('Remerch Category')),[Sales] - [Sales LY])

    I didn't need the bitfield in the end as I only had to work differently with the "Last Year" value

    However the real trick was to change the relationship from CubeData Final -> Lease Type to be a single direction filter rather than a both direction filter. That was why, when I filtered on a specific year/lease etc, I was losing the Last Year entry (as there were no records in CubeData with this entry) which meant that the measure never got a Lease Type Bitfield of 1 to actually calculate.

    Thanks for the education Michiel. I can now see how I can create this without a load of ETL!

    Sunday, July 17, 2016 4:42 PM