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
What would be the explanation for this behavior?
@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.
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.
@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.
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 I think you are on to something here, but the article on AutoExist
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 🙂
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.