Calculated table based on selection

[HR3038511]

Hi, 

I need to create a calculated table that shows all the Sales Agents for one period. The period needs be selected via a filter. Hence, when one period is selected the table should react and only show Sales Agents for that period.

When 2024 is selected the calculated table should show Sales Agent A & B. 

Here is the example file: https://easyupload.io/9b5cx9

Anybody an idea how to get it to work?

Thanks!

[v-achippa]

Hi @HR3038511,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin, @danextian and @Chewdata for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user's resolved your issue? or let us know if you need any further assistance.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @HR3038511,

We wanted to kindly follow up to check if the solution provided by the super user's resolved your issue.

If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @HR3038511,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user's resolved your issue.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Chewdata · Accepted solution]

Hey!

As my fellow uses stated, calculated tables and -columns are generated at the refresh and therefore will not react in the manner that you want. 

To get the results you want, create a seperated datetable. and connect it to your data.

Datetabel = 
ADDCOLUMNS (
CALENDAR (DATE(2024,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"MonthShort", FORMAT ( [Date], "mmm" ),
"MonthLong", FORMAT ( [Date], "mmmm" ),
"YearMonthNo", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
)

you can now use a slicer on the Year column of the date table to filter only years you want.

No Date column in Data?
If you don't have a date column in your data, you can also create a seperate table that contains the years and use that as a slicer.

You can create a slicer table with both Power Query (preferred) and DAX

Power Query:

let
    Source = Table.FromList({2023, 2024, 2025}, Splitter.SplitByNothing(), {"Period"}, null, ExtraValues.Error),
    ChangeType = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}})
in
    ChangeType

DAX Tabel:

Period = SUMMARIZE(YOURDATA, YOURDATA[Period])

Result:

If this solution helped solve your problem, please consider giving kudoes and mark it as a solution, so other users with similar problems can find the answer more quickly!

[danextian]

Hi @HR3038511 

Calculated columns and tables do not respond to slicer selections; they remain unaffected regardless of user interactions. Changes made in the report view do not affect them. To dynamically display the required rows based on slicer selections, use measures and visuals instead.

[lbendlin]

You cannot create calculated column or calculated tables from measures or user interaction (filters).   Only works the other way round.