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

[lbendlin]

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?

[Greg_Deckler]

@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.

[lbendlin]

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.

[Greg_Deckler]

@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.

[lbendlin]

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.

[AntrikshSharma]

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

[lbendlin]

@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 🙂