Calculated table that responds to context

[nicoenz]

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?

Break even =

ADDCOLUMNS(

    GENERATESERIES(0000000,10000000, 1000000),

    "FMC", -SUM('data table'[FMC Total]),

    "VMC", -(SUM('data table'[VMC Total]) / SUM('data table'[Quantity KG]) * [Value]) - SUM('data table'[FMC Total]),

    "NS", SUM('data table'[NSales]) / SUM('data table'[Quantity KG]) * [Value]

)
Regards and thanks in advance!!!

nico

[nicoenz]

Thanks @v-ssriganesh and @Greg_Deckler for your help!!!!!

It works like a charm

[v-ssriganesh · Accepted solution]

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.

[nicoenz]

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]

)

[Greg_Deckler]

@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