topic Re: Speed up PRODUCTX on FILTER ALLSELECTED in DAX Commands and Tips

Speed up PRODUCTX on FILTER ALLSELECTED

IK_TIM — Tue, 02 Jun 2020 20:48:33 GMT

I have a table of 3 columns (date, factor, return) and about 1.5 mil rows. I need to calc cumulative return in user-selected date range, for user-selected group of factors. My query gets soooo slow... How can I speed it up?

CUMULATIVE_RETURN = CALCULATE ( PRODUCTX(Returns_Master, [Return]/100 + 1 ),FILTER(ALLSELECTED(Returns_Master[Date]), Returns_Master[Date] <= MAX(Returns_Master[Date]))) - 1

Re: Speed up PRODUCTX on FILTER ALLSELECTED

AlexisOlson — Tue, 02 Jun 2020 21:05:54 GMT

Storing the max date as a variable might help so you only need to compute it once. See if this helps at all:

CUMULATIVE_RETURN = VAR MaxDate = MAX ( Returns_Master[Date] ) RETURN CALCULATE ( PRODUCTX ( Returns_Master, [Return] / 100 + 1 ), ALLSELECTED ( Returns_Master[Date] ), Returns_Master[Date] <= MaxDate ) - 1

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Tue, 02 Jun 2020 21:13:54 GMT

First, you should create a column (in Power Query, not in DAX!) that's equal to

Returns_Master[Factor] = Returns_Master[Return] / 100 + 1

Then you can write

CUMULATIVE_RETURN = var __currentDate = MAX ( Returns_Master[Date] ) return CALCULATE ( PRODUCTX ( Returns_Master, Returns_Master[Factor] ), KEEPFILTERS( Returns_Master[Date] <= __currentDate ), ALLSELECTED ( Returns_Master ) ) - 1

Best

Re: Speed up PRODUCTX on FILTER ALLSELECTED

IK_TIM — Wed, 03 Jun 2020 16:01:20 GMT

Thank you!

Re: Speed up PRODUCTX on FILTER ALLSELECTED

IK_TIM — Wed, 03 Jun 2020 16:20:00 GMT

Thank you, that makes sense! Not quite there yet. When I implement your solution (see code below), all selected factors show show up in the visual as having teh same value (the sum, see attached image). In my original, slow solution, individual factors selected showed up as individual lines.

CUMULATIVE_AXIOMA =

var __currentDate = MAX ( AxiomaReturns_Master[Date] )

RETURN

CALCULATE (

PRODUCTX (

AxiomaReturns_Master,

[ReturnPrepared]

KEEPFILTERS(

AxiomaReturns_Master[Date] <= __currentDate

ALLSELECTED ( AxiomaReturns_Master )

) - 1

Re: Speed up PRODUCTX on FILTER ALLSELECTED

AlexisOlson — Wed, 03 Jun 2020 22:31:58 GMT

The ALLSELECTED ( AxiomaReturns_Master ) part makes it so that the Factors cannot be distinguished.

You don't want that on the whole table but it should be OK just on the date column. I'd probably write it without the KEEPFILTERS function, personally.

CUMULATIVE_AXIOMA = VAR __currentDate = MAX ( AxiomaReturns_Master[Date] ) RETURN CALCULATE ( PRODUCTX ( AxiomaReturns_Master, AxiomaReturns_Master[ReturnPrepared] ), ALLSELECTED ( AxiomaReturns_Master[Date] ), AxiomaReturns_Master[Date] <= __currentDate ) - 1

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Thu, 04 Jun 2020 01:31:19 GMT

AlexisOlson

If you just put

ALLSELECTED ( AxiomaReturns_Master[Date] ),

in the formula and you don't put a filter directly on the Date column (but put it on, say, Month), you'll get a wrong result. This is because ALLSELECTED with just a column does nothing if there's no (shadow) filter on the column (the column is not being iterated).

Secondly, KEEPFILTERS is needed in case there's a (shadow) filter on a column different than Date, which can easily happen if a visual just shows months/weeks/quarters/semesters/years. So, a combination of ALLSELECTED( 'Table'[Column] ) and no KEEPFILTERS will not work correctly in all conditions, only is some special ones which you can't guarantee will be in force all the time.

EDIT: I do understand what you meant now.

I don't understand this statement of yours: "The ALLSELECTED ( AxiomaReturns_Master ) part makes it so that the Factors cannot be distinguished."

EDIT: The below, however, still stands.

ALLSELECTED( 'Table' ) does nothing else than just keeping all shadow filters on the table, if there are any (meaning at least one iteration happens on some column(s)), and gives you all the rows of the table that are visible during an iteration; if there's no iteration happening, it returns all the rows visible in the current context.

Thanks.

Best

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Thu, 04 Jun 2020 01:29:22 GMT

OK. I know why you get one graph instead of two different for 2 different factors. This is because your model is incorrect. It's not the measure that is, it's THE MODEL.

You have to create correct fact tables and correct dimensions. Currently, you're mixing those things and therefore it'll be next to impossible to write measures that will be behaving correctly in all circumstances.

Please re-factor your model into a proper star schema.

Best
D

Re: Speed up PRODUCTX on FILTER ALLSELECTED

AlexisOlson — Thu, 04 Jun 2020 02:37:17 GMT

Anonymous wrote:
OK. I know why you get one graph instead of two different for 2 different factors. This is because your model is incorrect. It's not the measure that is, it's THE MODEL.

Anonymous, this is a bit extreme. Denormalized tables can be easier to understand and work with depending on the situation, especially for beginners. I don't think it's fair to say the model is definitively "incorrect" in this case, even though having a separate date table is nearly always a good idea.

If OP were using other parts of the Date hierarchy, then yes, those would have to be dealt with appropriately but since the date table, the dimension table, and the fact table are all rolled into one, measures have to be written paying attention to individual columns rather than removing filters on the entire table.

Anonymous wrote:
ALLSELECTED( 'Table' ) does nothing else than just keeping all shadow filters on the table, if there are any (meaning at least one iteration happens on some column(s)), and gives you all the rows of the table that are visible during an iteration; if there's no iteration happening, it returns all the rows visible in the current context.

I don't agree with this. Specifically, ALLSELECTED does not return "all rows visible in the current context". The evaluation context includes context from slicers and filters as well as filter context produced by the visual. ALLSELECTED removes the filter context generated by the visual (but still respects slicers), This is why the Factors aren't separated into individual lines when inserted into the Legend box.

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Thu, 04 Jun 2020 03:35:22 GMT

AlexisOlson

Please read this to know what I'm talking about:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

because it does seem you don't fully understand how ALLSELECTED works. It's not an attack on you. This function is COMPLEX and there are not many people in this world that do understand its true internal workings. And to do, you have to know about shadow filter contexts. But the behavior of it is different when you use it with a column, with a table and without any argument. Very different.

The model is technically not incorrect but it's functionally incorrect and does not follow Best Practices. Calculating on such models is cumbersome and measures will have to be updated if attributes are added to the model later. You don't want to do this if you want to be professional. If the model is correct, you write a measure and IT WORKS IN ALL CIRCUMSTANCES. This is the difference between a bad and a good model. In a word, it does not matter what your level is: you have to build correct models if you want to "survive." Just as you have to build correct measures. What's the use of a measure that only sometimes gives you the right answer? If somebody wrote a measure like this for me in production, they wouldn't last long...

Best
D

Re: Speed up PRODUCTX on FILTER ALLSELECTED

AlexisOlson — Thu, 04 Jun 2020 14:39:31 GMT

Anonymous Thanks for the link. It's good to review that and indeed I was not completely technically correct with how I explained it.

To be a bit more precise, in IK_TIM's last posted measure, ALLSELECTED ( AxiomaReturns_Master ) removes the Factor filter context generated by the visual's Legend and restores the shadow filter context (from the slicers) whereas we only want to remove date-related filter context for the measure to function as expected.

ALLSELECTED operates in a similar way to ALL, which removes corresponding filters from the filter context when used as a CALCULATE modifier.

Since ALLSELECTED isn't used inside an iterator within the measure, it's behavior isn't terribly complex here.

What we typically teach users during our trainings is to use ALLSELECTED to retrieve the query context – that is, the context under which a pivot table or a report is executed – if and only if no iteration is happening.

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Thu, 04 Jun 2020 15:43:23 GMT

And this is precisely why you need to have A CORRECT MODEL in the first place.

Best
D

Re: Speed up PRODUCTX on FILTER ALLSELECTED

Anonymous — Thu, 04 Jun 2020 15:45:12 GMT

By the way... Each and every visual DOES generate an iteration (even if you can't see it in the measure), so technically you're not correct, either. There always IS an iteration and therefore ALLSELECTED does have a role to play. A BIG ROLE.

Best
D

Re: Speed up PRODUCTX on FILTER ALLSELECTED

AlexisOlson — Thu, 04 Jun 2020 16:38:12 GMT

Anonymous wrote:
By the way... Each and every visual DOES generate an iteration (even if you can't see it in the measure), so technically you're not correct, either. There always IS an iteration and therefore ALLSELECTED does have a role to play. A BIG ROLE.

That's exactly why I specified "within the measure" in my sentence and why the statement "ALLSELECTED( 'Table' ) does nothing else than just keeping all shadow filters on the table" is misleading.