Forum Discussion

ryan25r9's avatar
ryan25r9
Helper I
5 years ago

Help w/Tricky Issue: Disconnected Table DAX Bucketing

Hi there,   I've tried everything, & I'm at the end of my wits with this SSAS Tabular Model project. I think I'm getting accurate results now, but it's extremely slow and I can't pin down why. Any ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Question: Why can't you just pre-calculate all balances for all accounts for all days you're interested in? Then your measure would be dead simple and lightning fast, as would be the converted amount. But if you try to calculate the balances for every day and every account (and what have you) on the fly... well, that may cost you a lot.

 

I'm not a fan of the above DAX, either 🙂 It's too complex to say the least. I'm sure "There MUST be a better way," to paraphrase Raymond Hettinger.

ryan25r9's avatar
ryan25r9
Helper I
5 years ago

That's definitely something I started to consider lately. I'm going to look into how to structure that today and I'll follow-up here.

I still think there might be efficiency issues outside of that though. If I comment out this section and the reference to it:

 

VAR Aged =      FILTER( // Filter to dates within the Aging Bucket, based on the Due Date in relation to the As Of Date
                        VALUES('xDate - Document Due'[Document Due Date]),
                        VAR DaysOD =    DATEDIFF( 'xDate - Document Due'[Document Due Date], AsOfDate, DAY )
                        RETURN          COUNTROWS( // Second FILTER argument must be a Boolean value
                                                FILTER( 'Aging Buckets'
                                                        , 'Aging Buckets'[Lower Bound] <= DaysOD && DaysOD <= 'Aging Buckets'[Upper Bound]
                                                )
                                        ) > 0
                )

 

most things appear to go much faster, which seems odd to me.