Missing rows with calculate function

[martingaleh]

[FONT=&quot]I have a table that looks like this:
[/FONT]

[FONT=&quot]and I did[/FONT]
[FONT=&quot]z = calculate(sum([rev]),filter(all(table[zversion]),table[zversion]=2))[/FONT]
[FONT=&quot]Then I put zversion = 1 in the filter. But since this calculate removes the zversion in the filter context, if I put z in the values field, it should show every customer in the rows regardless of whether they have a zversion of 1 or not. Instead, it shows no customers in the rows and then in the grand total, it shows the grand total correctly I assume. how do I get the power pivot to show the custmer names in the rows. It doesn't.

There should be a c with 14 in srev_2. What is the rigth formula to show 12 in row b of srev_2 and 14 in row c of srev_2 regardless of what I put in the zversion filter[/FONT]

 

[Matt Allington]

Calculate modifies filter context, it doesn’t create missing data. In this case there is no data for cust c, Rev 1, and that is what is being correctly (not) displayed.

 

[martingaleh]

Calculate modifies filter context, it doesn’t create missing data. In this case there is no data for cust c, Rev 1, and that is what is being correctly (not) displayed.

Yes, but the filter(all([Table1[zversion]),Table1[zversion]=1)) blocks the zversion filter which is why company b sum is 12 and not 6. 12 is the value of b in zversion 2 and 14 is the value of c is zversion 2, so it should be there.

By the way. It's 2018. Are you going to make an updated version of your book? It's the easiest one to learn from. I'm really interested in what to do about the fact that we shouldn't use summarize anymore and summarizecolumns doesn't work for excel 2016 with an external filter context (whatever that means) yet:
https://www.sqlbi.com/articles/introducing-summarizecolumns/

 

[gazpage]

https://powerpivotpro.com/2017/08/autoexist-cross-table-filtering/

 

[martingaleh]

I'm not sure what I'm supposed to do with that link. It doesn't seem like a solution. It seems like a way of telling me Autoexist is a magical way microsoft minimizes the number of combinations it has to calculate over in an denormalized table by figuring out the number of possible combinations based on the filter context of the power pivot. This saves a lot of time. However, should a measure subvert this minimal combination of dimensional values, too bad. This is not a bug.

Oh further, if you want it to show everything, you have to trick the autoexist by putting your dimension of choice into another table and joining it back to your first table because "autoexist" isn't smart enough to guess the minimum set of dimensions if its in a traditional snowflake schema. Then use z = calculate(sum([rev]),filter(all(table2[zversion]),table2[zversion]=2))

By the way, this worked. That's the thing with the DAX language. The way I understand Dax, it's a bunch of hacks. If this isn't a hack, I don't know what is.

 

[Matt Allington]

By the way. It's 2018. Are you going to make an updated version of your book? It's the easiest one to learn from.

https://exceleratorbi.com.au/supercharge-excel/

...the fact that we shouldn't use summarize anymore and summarizecolumns doesn't work for excel 2016 with an external filter context

There is nothing wrong with SUMMARIZE. Just don't use it to add numeric columns. Instead use ADDCOLUMNS(SUMMARIZE()) as explained at SQLBI.COM

 

[martingaleh]

There is nothing wrong with SUMMARIZE. Just don't use it to add numeric columns. Instead use ADDCOLUMNS(SUMMARIZE()) as explained at SQLBI.COM[/QUOTE]

Yes, but then look at this:
https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

UPDATE 2017-01-30 : Excel 2016, Power BI and SSAS Tabular 2016 now have SUMMARIZECOLUMNS, which should replace the use of ADDCOLUMNS/SUMMARIZE described in this article. Read more in Introducing SUMMARIZECOLUMNS.

Which then says it doesn't work in all instances of excel 2016. I'm trying to prevent refactoring in excel 2020 or whenever the next one comes out