Forum Discussion
Calculated table that responds to context
Hi everyone,
I'm trying to create a calculated table with a fixed number of rows that responds to the context provided by some slicers. i will put the fields in a line visual to see where I have the break even.
So far I have below expression (calculated table) but it doesn't respond to the slicers!!!. Anyone has an idea on how to do it?
Regards and thanks in advance!!!
Hello nicoenz,
Thank you for reaching out to the Microsoft Fabric Forum Community. I have reproduced your scenario in Power BI Desktop as you described.Summary of What I Did:
- I created a disconnected axis table with GENERATESERIES for the X-axis values (0 to 10 million).
- I replaced the calculated table approach with measures that calculate FMC, VMC, and NS dynamically using SELECTEDVALUE from the axis table.
- I built a line chart using this axis table on the X-axis and the measures on the Y-axis.
- I tested with slicers filtering the Product field, and the chart values update dynamically as expected.
This approach meets your requirement to have a fixed number of rows (axis values) that respond to slicer/filter context.
For your reference, I am attaching the .pbix file demonstrating this solution so you can explore or adapt it further.
Thank you, Greg_Deckler for sharing valuable insights.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
4 Replies
Hello nicoenz,
Thank you for reaching out to the Microsoft Fabric Forum Community. I have reproduced your scenario in Power BI Desktop as you described.Summary of What I Did:
- I created a disconnected axis table with GENERATESERIES for the X-axis values (0 to 10 million).
- I replaced the calculated table approach with measures that calculate FMC, VMC, and NS dynamically using SELECTEDVALUE from the axis table.
- I built a line chart using this axis table on the X-axis and the measures on the Y-axis.
- I tested with slicers filtering the Product field, and the chart values update dynamically as expected.
This approach meets your requirement to have a fixed number of rows (axis values) that respond to slicer/filter context.
For your reference, I am attaching the .pbix file demonstrating this solution so you can explore or adapt it further.
Thank you, Greg_Deckler for sharing valuable insights.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.I even tried it with measures without success:
Break even =ADDCOLUMNS(GENERATESERIES(0000000,10000000, 1000000),"FMC", -[FMC final],"OPEX", -([VPC/kg total final] + [VSC/kg final] * [Value]) - [FMC final],"NS", [NS/kg final] * [Value])nicoenz Calculated tables cannot respond to context, they care calculated when the semantic model refreshes. In a measure, you would need to use a VAR to store the table since measures can only return scalars, not tables.:
Break even = VAR __Table = ADDCOLUMNS( GENERATESERIES(0000000,10000000, 1000000), "FMC", -[FMC final], "OPEX", -([VPC/kg total final] + [VSC/kg final] * [Value]) - [FMC final], "NS", [NS/kg final] * [Value] ) VAR __Result = SUMX( __Table, [NS] ) RETURN __Result
Thanks v-ssriganesh and Greg_Deckler for your help!!!!!
It works like a charm
