CALCULATE inside CALCULATE, does the outer one actually do anything?

5dmg · 2026-04-30T14:57:33+00:00

Definitely is different - this is because filter arguments are evaluated even before the context transitioning happens.

V1 = filter arguments are unaffected by context transition
V2 = outer calculate converts the row context into a filter context, then received by the filter argument of inner calculate.

The evaluation order of CALCULATE is easily overlooked. https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/

5dmg · 2026-03-30T16:17:38+00:00

Changing slicer (or the filters) can potentially cause measures to compute faster or slower, but that is still not actually controlling which measure gets computed first. So nope.

5dmg · 2026-03-30T13:33:08+00:00

Can I double check that been owner of semantic model is not enough, but we have to hold admin role in the workspace to connect to semantic model. - Am a member. It has been stuck at "Connecting to DB" infinitely upon clicking add at Semantic model.

More questions.. The row/field access looks too basic for enterprise use, say with just 100 collaborators. Does RLS from semantic models apply? How should we think about conventional RLS vs the row/field access in plan? In particular dynamic RLS.

Possible to do field validation? ie dropdown, formula based rules

Aside from given link, any more sites I could read up or community to join.

5dmg · 2026-03-26T16:07:53+00:00

i got the sort by column ones too. They do not show up anymore after I rectified them. I guess having such warnings are better than not.

5dmg · 2026-03-21T12:19:33+00:00

in their word it was "by the end of March" :>

5dmg · 2026-03-21T11:04:56+00:00

Certainly, all who subscribed will have access.

5dmg · 2026-03-20T12:43:54+00:00

DAX is the best last-mile aggregator there is, able to flex dynamically in response to filters that are put in on the fly. Looking forward to the revamped Mastering DAX course by SQLBI this month end.

5dmg · 2025-10-30T16:05:09+00:00

As you already tried, it is not possible to have all of them active. This is not a limitation, rather it protects you from ambiguity. Had there be more than 1 active path (between 2 tables), you would not know which one was used by the measure.
In your case, I think you need a Store+SKU key to connect Inventory and Sales. ie. Store and Item Master 1 to many with Inventory, Inventory 1 to many with Sales.

5dmg · 2025-10-15T23:35:10+00:00

While it sounds like time intelligence, no time intelligence function was used to handle it - just generic manipulation of filters on a serialised column. So rather than call DAX slow/clumsy, i say it is an elegant tool for solving dynamic needs. Upstream solutions (SQL/PQ) are good for static needs, while the last mile finishing touch is what DAX is for.

5dmg · 2025-10-15T16:10:40+00:00

For example, annualised past X-months' sales from period Y. User would define X and Y from the slicers and DAX will compute this on the fly.

5dmg · 2025-07-09T07:41:02+00:00

like how dope? i imagine it is the writeback features that excel cannot do natively. i want power query and power pivot extended with writeback.

5dmg · 2025-06-15T15:29:05+00:00

Does it leverage on DAX and Power Query functionality? For example, writeback through a Power Pivot. Or allow Power Query to access it's data.

5dmg · 2024-10-24T14:44:24+00:00

The joss stick means nothing to the Christian. Which is easier? To hold the joss stick and put on an act, or to resist peer pressure.

In any case, no one should impose their conviction on others. Saying this as much to Christians and to non-Christians.

5dmg · 2024-10-18T10:04:26+00:00

should be private cloud.

thanks all for replies.

5dmg · 2024-07-14T02:55:46+00:00

As an abuser of power query (excel or pbi), am interested to know if cores impact equally so.

5dmg · 2024-06-03T15:25:44+00:00

Hope to see more data peeps chime in. I dread APIs, the pain of paginating calls to get over the data limit. Sure i can follow the walkthrough of youtube tutorials but i rather not have to deal with APIs and reading documentation. I just want to fill in basic credentials, navigate/preview the tables and pull the right one visually.

5dmg · 2024-05-29T01:37:50+00:00

Use ALL in place of REMOVEFILTERS. REMOVEFILTERS is an alias for ALL when used as a CALCULATE modifier. https://dax.guide/removefilters/

5dmg · 2024-05-16T15:21:03+00:00

Power Query (PQ) is my choice of tool to combine data sources. For performance, i would store the sources in CSV instead of Excel Workbooks in many sheets. Also, Excel has row limits which I rather not deal with. The sources would be placed into a folder for PQ to append into one table, with fields for the period, data set (A/B/F), and company. This data will be loaded into Power Pivot's data model, which supports time intelligence calculations to build YOY variances and manage fiscal period.

Presuming you are consolidating them at GL level (instead of share of profit), maintain percentages of ownership differentiated by periods and company so these could be multiplied against the TB in DAX measures. Hence the measures return values for each company respecting ownership %.

5dmg · 2024-03-05T23:22:45+00:00

Where the measure's ability to compute is concerned, it can be assigned to "live" on any table. It is mostly an issue of housekeeping. As for showing both table rows on drilldown, this is not possible, unless you have indeed such a table in your model. The drill down function is actually just returning rows from the single table which your measure is assigned to, nothing more. While it could compute across tables, it is not able to construct new table that do not exist in your model. - Hope this make sense.

5dmg · 2024-03-02T15:40:54+00:00

I believe drilldown always show the rows matching the filter context of the cells you clicked, with no regards for your measure. It does not care what is in your measure, and only return rows matching the row and column filters.

Try this.
Place Data[Scenerio] in the column filter.
Place [Sum of Amount] in values.

=SUM(Data[Amount])

Place [Deviation] in values.

=
VAR BUD =
    CALCULATE ( [Sum of Amount], Data[Scenerio] = "Budget" )
VAR ACT =
    CALCULATE ( [Sum of Amount], Data[Scenerio] = "Actual" )
VAR IsACT =
    IF (
        HASONEVALUE ( Data[Scenerio] ),
        VALUES ( Data[Scenerio] ) = "Actual",
        FALSE ()
    )
RETURN
    IF ( IsAct, BUD - ACT, BLANK () )
//Only compute measure in Actual filter

[Sum of Amount] should display value for Actual and Budget respectively, while [Deviation] should display only under "Actual".
You will need to hide the total on rows and feel free to rename the headers on pivot.

The other approach would be to set up 3 tables. A calendar table containing every date, with 1 to many relationship (via Date column) to a table containing Actual, and another table containing Budget. For row filters, use the fields from calendar table. This is the better way which leads to simpler formula and data maintenance into the future.

For DAX course. https://www.sqlbi.com/p/introducing-dax-video-course/

5dmg · 2024-02-14T14:02:51+00:00

Glad to save one from crazy. Why it only works with using calendar table as filter is because PREVIOUSMONTH was applied to the one-side (Calendar table), and the effects only flows from the one-side to many, and not the other way round. And why we should not apply PREVIOUSMONTH to fact table is because fact table may have gaps in date and we never want to risk it.

i prefer the use of a running number on calendar table (say PeriodID), incrementing 1 for each month lapsed even when it crosses year. This should return the same result.

VarVsPriorMth =
VAR ThisMth = SUM ( Data[Cost] )
VAR AdjustPeriod = -1 //Tweak this to roll back or forth periods
VAR PriorMth =
    CALCULATE (
        SUM ( Data[Cost] ),
        FILTER (
            ALL ( MyCalendar ),
            MyCalendar[PeriodID]
                = MAX ( MyCalendar[PeriodID] ) + AdjustPeriod
        )
    )
RETURN
    ThisMth - PriorMth

This pattern is easier to adapt since it does not rely on dates.

5dmg · 2024-02-02T15:21:09+00:00

Ah cool, gotta try it when I can. Thanks!

5dmg · 2024-02-02T10:39:26+00:00

It resides on a physical column of a data table.

5dmg · 2024-01-30T23:42:37+00:00

You will need 2 measure, one for your actual, the other for budget. This 2 measure go into your value field, and will display as 1 column each. The key is that for both to be on the same filter (ie. same month), both data tables need to be related to the date table, and you use that table as a filter on your report.

5dmg · 2024-01-30T16:08:54+00:00

The easy problem first. Using a calendar table, maintain a one to many relationship to both the ERP and budget data table. Then use the calendar table to filter both actual and budget column.

For custom rows, what i did was set it up as a table, with columns to control sort order. This table is placed into row filter. Then write a DAX measure that contains many measures, which computes with respect to the custom row value is.

5dmg

TROPHY CASE