Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
D
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.
@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.
@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.
@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.
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
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
D
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
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |