Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
maybe someone will be able to advise. I have created field parameter as follows:
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
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:
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 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...
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |