topic Re: Use a dax calculated table as a measure. in DAX Commands and Tips

Use a dax calculated table as a measure.

jasemilly — Mon, 27 Oct 2025 12:28:25 GMT

I have created created a calculated table and it shows the data as I would like it.

Apart from it's not dynamic. So I wish to turn it into a measure and at the last step return the Growth value for the agent you can only have one Agent Full Name ever selected.

This is an example entry of the calculated table

Agent Full Name, Prev , Last, Mid, Growth

A , 01/09/2025 00:00:00, 18/09/2025 00:00:00, 09/09/2025 00:00:00, 0.789

I don't want to do anything with growth apart from display the value. It will eventually be displayed on area chart as a custom data label between the Prev and Last markers on the report.

The measure I created doesn't return anything. I thought this was due to the time stamp in the date field even though it was 00:00:00.

So, I connected my dates[date] field to the calculated table and created a table visual filtered to Agent A added the 'dates'date field and the growth field from the calculated table. This worked perfectly fine.

I am trying to use the calculated table logic in a measure and go one step further and filter by agent and when the current date is the same as what [mid] is then display.

I have broken my dax measure down in the power bi dax edittor and I have one row with the value I want see here, I am not showing the agents name but the field is populated.

This is the measue itself

Agent Growth for Date Test = VAR _Agent = SELECTEDVALUE('CallsReviewScore'[Agent Full Name]) VAR _CurrentDate = MAX('Date'[Date]) -- Recreate your calculated table logic dynamically VAR AgentDates = SUMMARIZE( 'CallsReviewScore', 'CallsReviewScore'[Agent Full Name], "Last", MAX('CallsReviewScore'[Created Date]), "Prev", CALCULATE( MAX('CallsReviewScore'[Created Date]), FILTER( 'CallsReviewScore', 'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name]) && 'CallsReviewScore'[Created Date] < CALCULATE( MAX('CallsReviewScore'[Created Date]), FILTER( 'CallsReviewScore', 'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name]) ) ) ) ) ) -- Add Mid and Growth columns VAR AgentDatesWithMid = ADDCOLUMNS( AgentDates, "Mid", VAR Prev = [Prev] VAR Last = [Last] RETURN IF( OR(ISBLANK(Prev), ISBLANK(Last)), BLANK(), Prev + INT(DATEDIFF(Prev, Last, DAY) / 2) ), "Growth", VAR Agent = [Agent Full Name] VAR Prev = [Prev] VAR Last = [Last] VAR PrevScore = CALCULATE( AVERAGE('CallsReviewScore'[Score]), FILTER( 'CallsReviewScore', 'CallsReviewScore'[Agent Full Name] = Agent && 'CallsReviewScore'[Created Date] = Prev ) ) VAR LastScore = CALCULATE( AVERAGE('CallsReviewScore'[Score]), FILTER( 'CallsReviewScore', 'CallsReviewScore'[Agent Full Name] = Agent && 'CallsReviewScore'[Created Date] = Last ) ) RETURN DIVIDE(LastScore - PrevScore, PrevScore) ) -- Filter to selected agent and current date VAR _Filtered = FILTER( AgentDatesWithMid, [Agent Full Name] = _Agent && int([Mid]) >= _CurrentDate && int([Mid]) <= _CurrentDate + 1 ) -- Return the Growth RETURN MAXX(_Filtered, [Growth])

So I the measure is working to a point as I can see the row when I use the dax edittor, the calculated table works and displays the value I want.

I believe the issue is a date time stamp issue I have tried to address this with the int([mid]) statment and the <= and the >=
I have placed the measue on a table with the dates'date' as well and placed on a card visual both filteres to the Agent and they return nothing.
But I am stuck at what to do next.

Re: Use a dax calculated table as a measure.

lbendlin — Mon, 27 Oct 2025 15:48:57 GMT

Instead of a calculated table you can use a variable inside your measure. The variable can be a table variable. The only requirement is that the measure itself needs to return a scalar value.

New Measure :=
var a = TOPN(10,'table')
RETURN COUNTROWS(a)

Re: Use a dax calculated table as a measure.

jasemilly — Mon, 27 Oct 2025 17:09:15 GMT

Thanks for replying but I don't understand how to use this in my situation, I am using a calculated table in my measure as I have proven this works, but is static. I thought it would be easy to filter the calculated table in the measure and return a scalar value.

Re: Use a dax calculated table as a measure.

lbendlin — Mon, 27 Oct 2025 17:18:05 GMT

I am using a calculated table in my measure

You can technically do that but it makes no sense. calculated tables are immutable, and measures depend on filter context. That is why I proposed to use table variables instead as they can be dynamic and can work inside measures.

Re: Use a dax calculated table as a measure.

Kedar_Pande — Tue, 28 Oct 2025 13:03:49 GMT

Remove the INT conversion and use direct date comparison:

Agent Growth for Date Test =
VAR _Agent = SELECTEDVALUE('CallsReviewScore'[Agent Full Name])
VAR _CurrentDate = MAX('Date'[Date])
VAR AgentDates =
SUMMARIZE(
'CallsReviewScore',
'CallsReviewScore'[Agent Full Name],
"Last", MAX('CallsReviewScore'[Created Date]),
"Prev", CALCULATE(
MAX('CallsReviewScore'[Created Date]),
FILTER(
ALL('CallsReviewScore'),
'CallsReviewScore'[Agent Full Name] = EARLIER('CallsReviewScore'[Agent Full Name]) &&
'CallsReviewScore'[Created Date] < MAX('CallsReviewScore'[Created Date])
)
)
)
VAR AgentDatesWithMid =
ADDCOLUMNS(
AgentDates,
"Mid", [Prev] + INT(DATEDIFF([Prev], [Last], DAY) / 2),
"Growth",
VAR PrevScore = CALCULATE(AVERAGE('CallsReviewScore'[Score]), 'CallsReviewScore'[Created Date] = [Prev])
VAR LastScore = CALCULATE(AVERAGE('CallsReviewScore'[Score]), 'CallsReviewScore'[Created Date] = [Last])
RETURN DIVIDE(LastScore - PrevScore, PrevScore)
)
RETURN
MAXX(
FILTER(
AgentDatesWithMid,
[Agent Full Name] = _Agent &&
[Mid] = _CurrentDate
),
[Growth]
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

Re: Use a dax calculated table as a measure.

jasemilly — Wed, 29 Oct 2025 10:03:41 GMT

Thanks for the response, originally the measure didn't include the int() function I only included to try and resolve issue same goes for the greater than and less than check.

I have just tried again, and I still have the issue

Re: Use a dax calculated table as a measure.

Anonymous — Mon, 03 Nov 2025 11:22:05 GMT

Hi jasemilly ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
Thank you

Re: Use a dax calculated table as a measure.

Anonymous — Mon, 10 Nov 2025 09:18:36 GMT

Hi jasemilly

I wanted to check if you had the opportunity to review to share the requested information , so that we could look into it. Please feel free to contact us if you have any further questions.

Thank you.

Re: Use a dax calculated table as a measure.

Anonymous — Mon, 10 Nov 2025 09:18:22 GMT

Hi jasemilly

May I check if this issue has been resolved? If not, Please feel free to contact us with the requested data if you have any further questions.

Thank you