Slicer not populating all values in composite model

[mdent]

I have a composite model that has been successfully working that uses relationships to the aggregation tables from Dual mode dimension tables.

I have introduced a new column into this model that I wish to use that I do not desire to have relationships to a dimension table, I want a 'GroupBy' aggregation setup so I pull in the column from my direct query table.

When I pull in this column value into a matrix, it shows a value per row of the highest precendent aggregation, instead of compressing to distinct values. Lets say it is gender - My matrix presents as 'M' 'M' 'M' 'F' 'F' 'F' - Instead of 'M' 'F'

If I drop a measure into this matrix, it does successfully compress to the distinct values.

What this is meaning is when I use this column in a Slicer, not all values are present - I am assuming a slicer can only accept the TopN values that it can see - Therefore on a big table, the latest values do not present. (Confirmed by if I start with a matrix and change the sort order - I can get the slicer to display the top values, but not the bottom values).

I can manage to get this to work(ish) - By making a mini-aggregation with a very high precedence to make this the table the slicer is pulling the values from - However, this seems to be a hack around a bug, rather than a good solution.

Why does this occur?

How do I make it work so only distinct values appear in my matrix? And therefore the slicer can find all possible values.

Have others faced this issue? What did you do to resolve it?

Thanks in advance.

[parry2k]

@mdent if it is a direct query, it only pulls x number of rows (depends on the source), check this post on limitation, and how many rows direct query pulls.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mdent]

Thanks @parry2k for your comment and link.

Every one of the aggregations I am using are in Import mode, which is why I was a touch confused why this issue was occuring.

Based on your link, it would make sense if my aggregations are in DQ mode. However, I would have expected the Import aggregations to take over to work out every value for the slicer (as all of those values are present in the model).

Additionally, normally if I pull in a column from a DQ table into a matrix in a report it will only show one row per possible distinct value (I guess as long as those values are in the first 1M rows). However, as soon as I add this column to a 'GroupBy' in 'Manage Aggregations' it then shows one value per row in one of my aggregations. (Just checking/confirming this atm, but my query in DQ is super slow - Will update if this is not strictly true)