Forum Discussion
Dynamic Data Tagging based on Slicer Selection
Hi everyone! 👋
I’m working on a Power BI report and need help creating a matrix visualization using a table with the following fields:
- Posting Period (formatted as YYYYMM)
- Entity
- Jurisdiction
- Value
I’ve added two slicers based on the Posting Period field:
- Opening Posting Period
- Closing Posting Period
Here’s what I’m trying to achieve:
- When a user selects an opening posting period (e.g., 202404), the Opening Balance should include all transactions from the beginning of time up to 202403.
- The Current Year Activity should include all transactions from 202404 thourgh the closing period 202409
Initially, I considered adding a calculated column called Type Tag to label each row as either "Opening Balance" or "Current Year Activity" based on the selected posting period. However, I learned that calculated columns can’t reference slicer selections.
📸I’ve attached a screenshot showing the desired matrix layout.
Question:
How can I dynamically tag the data as Opening Balance or Current Year activity based on slicer values and display in the matrix visualizations? Thanks in advance for your help!
Hi leonaxhero ,
Here's how you can achieve dynamic data tagging based on slicer selection:Solution Overview: Create 3 disconnected tables and use a dynamic measure to control what data shows based on user selections.
Step 1: Create Disconnected Tables
dax
// Table 1 - For start date selection
OP_Sel = DISTINCT(
SELECTCOLUMNS(
'SampleData',
"Posting Period", 'SampleData'[Posting Period]
)
)// Table 2 - For end date selection
CP_Sel = DISTINCT(
SELECTCOLUMNS(
'SampleData',
"Posting Period", 'SampleData'[Posting Period]
)
)// Table 3 - Category tags
TagTable = DATATABLE(
"Type Tag", STRING,
{
{"Opening Balance"},
{"Current Year Activity"}
}
)
Step 2: Create Dynamic Measuredax
Dynamic Value = VAR SelectedTag = SELECTEDVALUE(TagTable[Type Tag])
VAR StartDate = MIN('OP_Sel'[Posting Period])
VAR EndDate = MAX('CP_Sel'[Posting Period])
VAR CurrentRowDate = MAX('SampleData'[Posting Period])RETURN
SWITCH(
TRUE(),
SelectedTag = "Opening Balance" && CurrentRowDate < StartDate,
SUM('SampleData'[Value]),
SelectedTag = "Current Year Activity" && CurrentRowDate >= StartDate && CurrentRowDate <= EndDate,
SUM('SampleData'[Value]),
BLANK()
)
How it works:Users select date ranges with OP_Sel and CP_Sel slicers
TagTable values switches between "Opening Balance" (shows data before start date) and "Current Year Activity" (shows data within selected range)
The measure dynamically filters data based on these selectionsThanks
4 Replies
Hi leonaxhero,
please help me clarify what I am confused about
1 - "Opening Balance", what do you mean with this? You want to calculate something I assume (a measure), or you just want to see rows of the sales table?
2 - "Current Year Activity", as in nr 1 do you mean you just want to see the rows?
3 - Will the selectin on the slicer o Opening Balance period be mandatory or might it be that there is no selection there? And in this case do you want to see all the hisory past and future?
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi! Raju_17_97's solution solved my problem, but i really appreicate your response!
Hi leonaxhero ,
Here's how you can achieve dynamic data tagging based on slicer selection:Solution Overview: Create 3 disconnected tables and use a dynamic measure to control what data shows based on user selections.
Step 1: Create Disconnected Tables
dax
// Table 1 - For start date selection
OP_Sel = DISTINCT(
SELECTCOLUMNS(
'SampleData',
"Posting Period", 'SampleData'[Posting Period]
)
)// Table 2 - For end date selection
CP_Sel = DISTINCT(
SELECTCOLUMNS(
'SampleData',
"Posting Period", 'SampleData'[Posting Period]
)
)// Table 3 - Category tags
TagTable = DATATABLE(
"Type Tag", STRING,
{
{"Opening Balance"},
{"Current Year Activity"}
}
)
Step 2: Create Dynamic Measuredax
Dynamic Value = VAR SelectedTag = SELECTEDVALUE(TagTable[Type Tag])
VAR StartDate = MIN('OP_Sel'[Posting Period])
VAR EndDate = MAX('CP_Sel'[Posting Period])
VAR CurrentRowDate = MAX('SampleData'[Posting Period])RETURN
SWITCH(
TRUE(),
SelectedTag = "Opening Balance" && CurrentRowDate < StartDate,
SUM('SampleData'[Value]),
SelectedTag = "Current Year Activity" && CurrentRowDate >= StartDate && CurrentRowDate <= EndDate,
SUM('SampleData'[Value]),
BLANK()
)
How it works:Users select date ranges with OP_Sel and CP_Sel slicers
TagTable values switches between "Opening Balance" (shows data before start date) and "Current Year Activity" (shows data within selected range)
The measure dynamically filters data based on these selectionsThanks
This works. Thank you!
