topic Re: Can a scalar measure increase the row count of a table visual that is based on a single table? in DAX Commands and Tips

Can a scalar measure increase the row count of a table visual that is based on a single table?

lbendlin — Fri, 04 Sep 2020 02:26:27 GMT

This one has me scratching my head. I always thought that scalar measures can only be an additional attribute to an existing physical row of data, or contribute to the aggregations. However below is a case where adding a measure to a table visual will increase the number of rows displayed in the visual.

Data Source SoC:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUUJGRkqxOnAJx7yUotRyZGlDrNK+iUWVBEyAKvFKzE0tJkGdV35xBpAyIEatR2piSUZqEUHlpcUlmXnEuAGm0CsxL7WEaGN9M5MzUnNyUtE1ANVlg/SlJOZiD05UBQGpJWC/4DImLz01JxG/QTAlYKcmws2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [H1 = _t, H2 = _t, H3 = _t, H4 = _t, H5 = _t, #"Span of control" = _t]) in Source

Table visual:

Measure:

SOC2 = Coalesce(CALCULATE(DISTINCTCOUNT(SoC[H5]),SoC[H5]<>""),CALCULATE(DISTINCTCOUNT(SoC[H4]),SoC[H4]<>""))

(the measure is not finished, but that's besides the point - I think)

Adding the measure as a table visual value gives:

What would be the explanation for this behavior?

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

Greg_Deckler — Fri, 04 Sep 2020 02:48:27 GMT

lbendlin - You have something strange going on there. Seems like some odd combination of weird internal DAX joins or something because the rows that show up are completely invalid hierarchies that aren't really "there"

Are we still struggling with Anonymous 's stuff? I was helping her with some things earlier. Do we still have it in 5 seperate hierarchy columns? I was suggesting that maybe unpivoting those columns would make things easier.

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

lbendlin — Fri, 04 Sep 2020 03:10:12 GMT

No, I solved her question but the measure I came up with (based on SWITCH()) felt "wrong" and in need of improvement. COALESCE() is technically very similar to SWITCH() - kinda like SWITCH(NOT BLANK(),...) so I thought to give that a try.

COALESCE() seems to behave well with more than two parameters. But with the two parameters in my example it seems to struggle. It is well possible that CALCULATE() plays a role here, but as far as I understand it CALCULATE cannot produce a table or a cross join. Most likely I just don't understand DAX.

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

Greg_Deckler — Fri, 04 Sep 2020 03:19:08 GMT

lbendlin - No, it's COALESCE. Now that I am looking at it, COEALESCE essentially returns the first non-blank value for things. So that's why it is getting the hierarchies all weird. Since you are coalescing it's finding the H5 values even for the rows where the hierarchies end at 3. If you notice, only H4 and H5 are ever blank and if H5 is blank, then H4 is also blank or has a value that is predicated by an H3. So the only gaps you can possibly have are when sometimes the hiearchy in a row stops at H3 and some rows continue on to H5.

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

lbendlin — Fri, 04 Sep 2020 03:23:51 GMT

Note that none of the cells are blank - they are empty strings. Might be worth replacing these with actual blanks to see if it behaves different.

Regardless, if COALESCE returns the first non-blank value then how can that one single value increase the row count? From the looks of it COALESCE returns two values in this scenario.

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

AntrikshSharma — Sat, 05 Sep 2020 13:52:43 GMT

lbendlin I think this has something to do with AutoExist, maybe marcorusso can help with the behaviour here.

Re: Can a scalar measure increase the row count of a table visual that is based on a single table?

lbendlin — Sat, 05 Sep 2020 21:32:56 GMT

AntrikshSharma I think you are on to something here, but the article on AutoExist

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

specifically mentions SUMMARIZECOLUMNS vs CALCULATETABLE, and seems to imply that AutoExist produces fewer results, not more (although to be fair my issue does not create more results, only more table visual row renderers).

Which query is produced by COALESCE? I whittled the issue down to just show H3, H4 and H5 plus the SOC2 measure.

// DAX Query DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL(ROLLUPGROUP('SoC'[H3], 'SoC'[H4], 'SoC'[H5]), "IsGrandTotalRowTotal"), "SOC22", 'SoC'[SOC2] ) VAR __DS0PrimaryWindowed = TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'SoC'[H3], 1, 'SoC'[H4], 1, 'SoC'[H5], 1) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'SoC'[H3], 'SoC'[H4], 'SoC'[H5]

trying to figure out if this gives a clue 🙂