Paginated Report Aggregating and sort orders

Power BI
1

Hi guys
I’m writing a paginated report which requires aggregation. I have managed this and the report (early days!) is presenting the right stuff.

But when I go to set the sort order on the matrix (col headed RepKey), this is not working because the MDX is creating nulls in the RepKey to generate the aggregation amounts. See screenshot.

MDXResults
MDXResults518×566 9.54 KB

I can work around this without MDX by generating 2 Dax queries one each for detail and aggregation, But this can get clunky and is not ideal.

Any help on getting sort order to ignore the nulls in Paginated Report builder would be cool. Or any other workaround?

I can also see myself heading into issues where we need a 2 or 3 tier aggregation in MDX. Any ideas how to do this?

Cheers
Pete

2

Lets stop on this one and not get into the murky world of MDX.

Rather than use the paginated report designer, I’ve taken to writing the DAX in desktop or Studio and pasting it back into the paginated report. I had tried that before but was struggling with parameters in the pag report. Fantastic link here New tab (databayes.co.uk) explains parameter set up.

With SUMMARIZECOLUMNS and ROLLUPADDISSUBTOTAL my null issue goes away.
However it does create another issue on a post I’ll raise in a mo.

So lets close this one down
Pete