Property IsAvailableforMDX set to false test so you can see where it breaks

[TinyTimothy2]

Set IsAvailableforMDX set to false for ORDER_ID and couple other items in FACT.
In PBI - Add matrix with customer and total orders
add table that acts a line of details for each order....make order_id is in it.
filter page for a day just to get a smaller number of records.

Everything shows as expected.
Click matrix. Great details table filters for order by customer

Now click a ROW in the details section. You WILL get the error in the MATRIX visual (or table) basically saying tough luck.
Luckily with TE3 you can make the change fast to test
Set it back to True and just recalc (not process the table) only take a few secs for 100 Mil rows.

[otykier]

Sorry, is this a question or just an observation? Side effects from setting IsAvailableInMdx have been known for a while. The bottom line is that it should be used with caution.

[TinyTimothy2]

I have read that, any other articles. I do not see where it actually discusses this scenario, or how to interpret what was written in the article that i would have known my side effect should have been expected.
It is confusing particularly when so many articles say, if you don't use excel and only use power bi, they can all be set to false.

I have also set IsAvailableforMDX set to false on FK to get distinctcounts, and i do not see any difference, despite the warnings in the article

[otykier]

It's a complex topic and unfortunately Microsoft hasn't provided any good documentation about this.

I believe the general rule of thumb is to set it to false only on columns used for aggregations (i.e. [Sales Amount], [Quantity], etc.). Incidentally, these are typically also the columns with the highest cardinalities, so you would obtain the highest savings (processing time, in-memory size), by disabling attribute hierarchies on these columns.

On all other columns (key columns, dimension attributes, columns used for sorting other columns, etc.), I would recommend leaving IsAvailableInMDX as true.