dax issue

[powerbiforever7]

hi, 

i have a cost measure. from this measure i want to create a table where i can make an incremental 5 up / down of this measure.

i use below measure, however when i change the date filter then my cost does not change, it takes into account all the period. I want it to take into account the date filter.

Simulated Cost Table =

VAR BaseCost = [Cost]  -- use your selected base cost measure or variable

RETURN

ADDCOLUMNS(

    GENERATESERIES(-5, 5, 1),

    "BaseCost", BaseCost,

    "SimulatedCost",

        VAR Step = [Value]

        RETURN BaseCost * (1 + Step * 0.05)

)

thanks in advance.

[Poojara_D12]

Hi @powerbiforever7 

You're trying to simulate variations of a measure called [Cost] by generating a table with a ±5 step range in 5% increments. The Simulated Cost Table you wrote uses GENERATESERIES(-5, 5, 1) to produce steps from -5 to +5, and for each step, it calculates a simulated cost by applying that percentage shift to the base [Cost]. However, the issue you're facing is that the [Cost] measure isn't responding to the date filters applied in your report. This happens because when you assign VAR BaseCost = [Cost] outside of the ADDCOLUMNS() row context, it gets evaluated once in the context of the overall visual or page, without considering each row's filter context—especially the current date selection.

To fix this, you should move the calculation of [Cost] inside the row context of ADDCOLUMNS by wrapping it in a CALCULATE function so that it's dynamically recalculated for each row based on the current report filters, including dates. Here's how you can modify it:

Simulated Cost Table =
ADDCOLUMNS(
    GENERATESERIES(-5, 5, 1),
    "BaseCost", CALCULATE([Cost]),
    "SimulatedCost",
        VAR Step = [Value]
        RETURN CALCULATE([Cost]) * (1 + Step * 0.05)
)

This version ensures that [Cost] is recalculated within the appropriate filter context for each row, respecting the date and any other filters applied in the report.

[v-achippa]

Hi @powerbiforever7,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam, @Jihwan_Kim and @FBergamaschi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved?

If not please provide sample data that covers your issue or question completely, in a usable format.
Do not include sensitive information and anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @powerbiforever7,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @powerbiforever7,

As we haven’t heard back from you, as per community guidelines we are closing this thread for now as you haven't provided the sample data. If you have any further issue, please raise a new thread in the community forum, we are ready to assist you on it.

Thank you for being part of Microsoft Fabric Community.

Thanks and regards,

Anjan Kumar Chippa

[FBergamaschi]

I do not understand the question

As Jihwan_Kim wrote, a calculated table cannot help you if you expect to see values changing in it based on selections

So please can you clarify what you want to obtain showing us an example in which you use the measure (take a screenshot and paste it here ) and what is the desired table you want to create and for what purpose?

So we can help

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but in my opinion, when you try creating calculated table, it is created when the data model refreshes, and it does not dynamically change when you adjust filters or slicers on the report.

Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

[bhanu_gautam]

@powerbiforever7 , Try using

Simulated Cost Table =
VAR BaseCost = CALCULATE([Cost]) -- use your selected base cost measure or variable
RETURN
ADDCOLUMNS(
GENERATESERIES(-5, 5, 1),
"BaseCost", BaseCost,
"SimulatedCost",
VAR Step = [Value]
RETURN BaseCost * (1 + Step * 0.05)
)