Mysterious blanks

[rob_wherewe]

I have a database with unique customers and volumes sold to them. I want to derive a measure that sums "average monthly" volumes in past periods. Putting aside the denominator for a moment - i.e. just getting the volume per customer in the past period - I run into a mysterious problem. 

If I do a more simple formula such as...CALCULATE(sum(Market[Volume]),sameperiodlastyear( Calender[Date].[Date]))

...everything works exactly as expected. When I a virtual table, e.g....

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Customer, Customer[ID] ),
        "Calc1", CALCULATE (
            SUM ( Market[Volume] ),
            SAMEPERIODLASTYEAR ( Calender[Date].[Date] )
        )
    ),
    [Calc1]
)

....everything also works exactly as before except for one MYSTERIOUS case. If a customer does not have any sales say September 2019, it returns a BLANK, even though I am expecting it to return the September 2018 number - which it does do in the simple equation above. 

Appreciate, truly, any help....

[Nathaniel_C]

Hi @rob_wherewe ,
I was just watching a video about this issue having to do with relationships between tables and that DAX engine does this on purpose. If you go here Possible solution and read the two solutions, it may be helpful.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[rob_wherewe · Accepted solution]

Hi Nathaniel_C

So, I did not read through everything on that link but one of their solutions was - for each Customer, adding blank rows to the volume table for all months in which they did not have sales. That would work, I guess, but it would also change my model and I would have to adjust OTHER measures which currently rely on those rows being entirely missing.

I did play around more and came up with two more solutions. 

So the first thing I tried was a simple summarize function - it produced same error. See below.

sumx ( summarize (Customer,Customer[ID],"Calc1",

CALCULATE(sum(Market[Volume]),sameperiodlastyear( Calender[Date].[Date])),

[Calc1])

The next thing I tried was the AddColumns version I started this post with - also same error. See below. 

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Customer, Customer[ID] ),
        "Calc1", CALCULATE (
            SUM ( Market[Volume] ),
            SAMEPERIODLASTYEAR ( Calender[Date].[Date] )
        )
    ),
    [Calc1]
)

I then tried using the simple summarize function with a column from the Customer table that was at a higher level of aggregation - Customer[Groups] - also was NOT the column linking to the other tables. Also did not work.

But trying that together with the AddColumns version DID work. It is also the case that using that formula in a visual with Customer[ID] in rows also worked. So I count that as a solution. See below. 

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Customer, Customer[Group] ),
        "Calc1", CALCULATE (
            SUM ( Market[Volume] ),
            SAMEPERIODLASTYEAR ( Calender[Date].[Date] )
        )
    ),
    [Calc1]
)

Yet probably the most robust solution was to take the time-intellegence function out of the Summarize/ Add Columns portion of the equation, and rather to stick in a Calculate call, wrapped round the whole thing. That also worked, and I guess its the most robust solution. See below (also worked with the AddColumns version). 

Calculate(

sumx ( summarize (Customer,Customer[ID],"Calc1",

sum(Market[Volume])),[Calc1]),

sameperiodlastyear( Calender[Date].[Date]))