Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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.
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
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
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
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
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
@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)
)
Proud to be a Super User! |
|
User | Count |
---|---|
25 | |
11 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
7 |