Calculated table column values changes on slicer selection

[Szokens]

Hi,

maybe someone will be able to advise. I have created field parameter as follows:

Timeframe_table = {

    ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),

    ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),

    ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),

    ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)

and within this table I have additional measure as follows:

selection_timeframe = SELECTEDVALUE(Timeframe_table[Parameter Order])

"Timeframe" field, I have put on X-axis of barchart of my report, so using slicer build also from "Timeframe" I can change dimension dynamically to look on stats across day/week/month/quarter.

Then I have my fact table dmn_email, with four calculated columns let's call them C1, C2, C3, C4. Each row of these column have one of two values (flags): "New" or "Returning" basing on different conditions.

In my barchart, I am counting users. I wanted then to put a field in a "Legend" which will tell me how many users are "New" and how many are "Returning". Legend values shall come from one of C1-C4 columns basing on selection in initial slicer. I cannot put a measure in a Legend. I tought to use calculated table formula, which will return one column with rows (NOT DISTINCT) coming from C1 to C4 basing on selection from intial slicer, so I could use column from this calculated table in a legend. Is it even possible?

[v-tejrama]

Hi @Szokens ,

Thanks for reaching out to the Microsoft fabric community forum.

Absolutely, this approach is achievable using the disconnected table technique in Power BI.
I replicated the scenario by creating a disconnected table named ColumnSelector with values C1 to C4 using the following DAX:

ColumnSelector = DATATABLE(
   "ColumnName", STRING,
   {
       {"C1"},
       {"C2"},
       {"C3"},
       {"C4"}
   }
)

This table powers a slicer, allowing users to select which column to analyze. I then developed two dynamic measures to count users flagged as "New" or "Returning" based on the selected column:

Count_New =
VAR sel = SELECTEDVALUE(ColumnSelector[ColumnName])
RETURN
   SWITCH(
       TRUE(),
       sel = "C1", CALCULATE(COUNTROWS(dmn_email), dmn_email[C1] = "New"),
       sel = "C2", CALCULATE(COUNTROWS(dmn_email), dmn_email[C2] = "New"),
       sel = "C3", CALCULATE(COUNTROWS(dmn_email), dmn_email[C3] = "New"),
       sel = "C4", CALCULATE(COUNTROWS(dmn_email), dmn_email[C4] = "New")
   )

Count_Returning =
VAR sel = SELECTEDVALUE(ColumnSelector[ColumnName])
RETURN
   SWITCH(
       TRUE(),
       sel = "C1", CALCULATE(COUNTROWS(dmn_email), dmn_email[C1] = "Returning"),
       sel = "C2", CALCULATE(COUNTROWS(dmn_email), dmn_email[C2] = "Returning"),
       sel = "C3", CALCULATE(COUNTROWS(dmn_email), dmn_email[C3] = "Returning"),
       sel = "C4", CALCULATE(COUNTROWS(dmn_email), dmn_email[C4] = "Returning")
   )

Next, I set up a static legend table:

LegendValues = DATATABLE(
   "Flag", STRING,
   {
       {"New"},
       {"Returning"}
   }
)

Finally, I consolidated the measures into one for the visual:

Final Count =
SWITCH(
   SELECTEDVALUE(LegendValues[Flag]),
   "New", [Count_New],
   "Returning", [Count_Returning]
)

By using the ColumnSelector slicer, placing LegendValues[Flag] in the legend or axis, and using Final Count as the value, users can easily switch between columns (C1–C4) and see accurate "New" vs "Returning" counts.
This method is highly effective, even though dynamic columns cannot be placed directly in the legend.

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[Szokens]

Hi @v-tejrama , your solution is nice however it count rows for "New" or "Returning" from whole dmn_email table & specific C1-C4 column. I forgot to mention my vizual will have additional slicers like:

dmn_email[sent_date],

v_sdp_employee[business_title]

v_sdp_employee[business_area],

dmn_recipient[internal_external].

relations:

v_sdp_employee 1 - * dmn_email

dmn_recipient 1 - * dmn_email

This was one thing, second is that I think this disconnected table lost date info to which "New" / "Returning" flag is connected to (so when looking on day by day or month by month ect. like I mentioned initially I could connect these flags with specific dates).

To have more background, C1 - C4 columns are actually date columns from dmn_date table (dmn_date 1 - * dmn_email) as follows:

C1 - is specific calendar date

C2 - is a last day of the week of C1

C3 - is last day of the month of C1

C4 - is last day of the quarter of C1

Graph I would like to prepare and change dimnension to daily/weekly/monthly/quarterly dynamically, shall look like this:

[v-tejrama]

Hi @Szokens ,

Enable dynamic time granularity options (Daily, Weekly, Monthly, Quarterly) on the X-axis. Display user counts split by "New" and "Returning" based on different flag columns (C1–C4). Keep full filter context, including sent_date, business title, area, and internal/external. Address Power BI’s limitation regarding placing measures directly in the legend.

FIELD PARAMETER FOR TIME AXIS (Already Set Up)
Timeframe_table = {
    ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),
    ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),
    ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),
    ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)
}

Assign Timeframe_table[Timeframe] to the visual’s X-axis.

CREATE DISCONNECTED FLAG SOURCE SELECTOR TABLE
FlagSourceSelector = DATATABLE(
   "FlagSource", STRING,
   {
       {"C1"},
       {"C2"},
       {"C3"},
       {"C4"}
   }
)

Use this table in a slicer so users can choose which C column (C1–C4) to analyze.

LEGEND TABLE
LegendValues = DATATABLE(
   "Flag", STRING,
   {
       {"New"},
       {"Returning"}
   }
)

Use this table for the bar chart legend.

FINAL MEASURE WITH CONTEXTUAL LOGIC
User Count by Flag =
VAR SelectedFlag = SELECTEDVALUE(FlagSourceSelector[FlagSource])
VAR SelectedLegend = SELECTEDVALUE(LegendValues[Flag])
VAR Result =
    SWITCH(
        SelectedFlag,
        "C1", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C1_Flag] = SelectedLegend
            ),
        "C2", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C2_Flag] = SelectedLegend
            ),
        "C3", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C3_Flag] = SelectedLegend
            ),
        "C4", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C4_Flag] = SelectedLegend
            )
    )
RETURN Result

Update C1_Flag, C2_Flag, etc. with your actual column names containing "New" or "Returning".

VISUAL CONFIGURATION
X-axis: Timeframe_table (calendar_date / week / month / quarter)

Legend: LegendValues[Flag]
Values: Measure: User Count by Flag

Thank you.

[Szokens]

This is great, I have modifiet it a bit and it works perfectly. I will mark this as a solution, but by the way can you give me additional advice on one flexible feature? 

I would like give to user ability which set of values fall into disconnected table "LegendValues".

I have updated your below formula so it have two variables:

LegendValues =

VAR NewReturning = DATATABLE(

   "Flag", STRING,

   {

       {"New"},

       {"Returning"}

   }

)

VAR BusinessArea = SELECTCOLUMNS(SUMMARIZECOLUMNS(v_sdp_dim_employee_curr[gcrs_busin_area_name]),"Flag",v_sdp_dim_employee_curr[gcrs_busin_area_name])

RETURN

SWITCH(TRUE,

Parameter_Breakdown_By[Breakdown by]="New vs Returning",NewReturning,

Parameter_Breakdown_By[Breakdown by]="Business Area",BusinessArea)

I have created also additional disconnected table as following:

Parameter_Breakdown_By = DATATABLE("Breakdown by",STRING,{{"New vs Returning"},{"Business Area"}})

When after "Return" clause I put only (x)BusinessArea or (x)NewReturning it gives me expected result, but underlined part of "LegendValues" seems doesn't be correct - i got an error "the expression specified in the query is a not valid table expression".

I would like to have influence on the result of "LegendValues" when switching values of "Breakdown by" in slicer.

[v-tejrama]

Hi @Szokens ,

Thank you for the follow up.

Here's a reliable tip for dynamically switching legends or axis in Power BI using slicer selections, such as toggling between “New vs Returning” and “Business Area.” While I initially attempted a DAX measure with SWITCH and SELECTEDVALUE, it resulted in an error since measures can't be used as fields in visuals.

The optimal approach is to use Field Parameters. By creating a parameter with both Status and Business Area columns from the EmployeeData table through Modeling > New Parameter > Fields, Power BI automatically generated a slicer. I then applied this parameter to the X-axis of a Stacked Column Chart, with Total Sales as the Y-axis.

Switching the slicer between Status and Business Area now updates the chart instantly—no need for complex DAX or bookmarks. Field Parameters are a powerful solution for making your visuals more adaptable.

Please find the attached pbix file for your reference.

Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @Szokens ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tejrama]

Hi @Szokens ,

I hope the information provided has been useful. Please let me know if you need further clarification

Thank you.

[Szokens · Accepted solution]

Hi!

This is actually fantastic, I have adapted your ideas into my use case and here is the output. One barchart changing dimensions and breakdown type dynamically ( example screenshots):

Below I put formulas for each field I have included in the visual settings if anyone would be interested in this solution:

i) X Axis - "Timeframe_Table[Timeframe]", field from disconnected field parameter table (data comes from date table related to our fact table in relation [dmn.date] 1 - * [dmn.email]):

Timeframe_table = {

    ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),

    ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),

    ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),

    ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)

ii) Y Axis - dmn.email[User_Count_by_Flag]

User_Count_by_Flag =

VAR SelectedFlag = SELECTEDVALUE(Timeframe_table[Parameter Order])

VAR SelectedLegend = SELECTEDVALUE(LegendValues[Flag])

VAR Result0 =

    SWITCH(

        SelectedFlag,

        0, CALCULATE(

                DISTINCTCOUNT(dmn_email[sender_email]),

                dmn_email[New/Old User day] = SelectedLegend

            ),

        1, CALCULATE(

                DISTINCTCOUNT(dmn_email[sender_email]),

                dmn_email[New/Old User week] = SelectedLegend

            ),

        2, CALCULATE(

                DISTINCTCOUNT(dmn_email[sender_email]),

                dmn_email[New/Old User month] = SelectedLegend

            ),

        3, CALCULATE(

                DISTINCTCOUNT(dmn_email[sender_email]),

                dmn_email[New/Old User quarter] = SelectedLegend

            )

    )

VAR Result1 = DISTINCTCOUNT(dmn_email[sender_email])

RETURN

SWITCH([Selected_Breakdown_by],

0,Result0,

1,Result1)

iii) Legend - "Parameter_Breakdown_By2[Parameter_Breakdown_By2]", field from disconnected field parameter table (data comes from another disconnected table, formula provided below + employee table related to our fact table in relationship like [v_sdp_dim_employee_curr] 1 - * [dmn_email] )

Parameter_Breakdown_By2 = {

    ("New vs Returning", NAMEOF('LegendValues'[Flag]), 0),

    ("Business Area", NAMEOF('v_sdp_dim_employee_curr'[gcrs_busin_area_name]), 1)

}

Other required fields:

iv) disconnected table as follows:

LegendValues =
DATATABLE(
"Flag", STRING,
{{"New"},{"Returning"}}
)

*i am not posting all formulas fields dmn_email[New/Old User...] just an example on "Weekly" field:

v) Min sent date weekly = CALCULATE(MIN(dmn_date[last_day_of_week]),ALLEXCEPT(dmn_email,dmn_email[sender_email]))

vi) New/Old User week =

IF(related(dmn_date[last_day_of_week])>dmn_email[Min sent date weekly],"Returning","New")

thanks again for support!

[Greg_Deckler]

@Szokens See if this helps. I believe you want the disconnected table trick here: https://www.bing.com/search?pglt=427&q=attendance+and+disconnected+table+trick&cvid=d8120100462e4048...