Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey there!
So what we have here are:
1) "Status" chart
2) "Readiness" column in the table
Both status chart sections and flag icons in the column are measures. A specific table is created for the chart, without any relationship to the other tables.
What I want to:
Whenever a user clicks on a section (say red, for example), the column in the table should display only the projects with red flag icons:
For now, whenever I click on whatever section of the chart - the column doesn't get filtered.
Chart table:
1) table itself
2) color conditions
Color =
SWITCH('Status Chart Filter Table'[Flag],
"FlagHigh", "At Plan",
"FlagMedium", "Behind Plan",
"FlagLow","At Risk",
"FlagBlack", "Other"
)
3) table expression
Status Chart Filter Table =
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 1, 4, 1 ), "Sort", [Value] )
VAR T2 =
ADDCOLUMNS (
T1,
"Flag", SWITCH ( [Sort], 1, "FlagHigh", 2, "FlagMedium", 3, "FlagLow", 4, "FlagBlack" )
)
RETURN
T2
4) count the number of a particular color:
Flag Count =
VAR CurrentFlag =SELECTEDVALUE('Status Chart Filter Table'[Flag] )
VAR T1 = ADDCOLUMNS ( VALUES ( 'All Project v2'[Project Name] ), "@Flag", [stat chart] )
VAR T2 = FILTER ( T1, [@Flag] = CurrentFlag )
RETURN COUNTROWS ( T2 )
Conditional formatting expression used to mark "Readiness columns" with respective flags (if needed):
_Overall_Status_Flag =
VAR Unit_Readiness = [Upcoming Unit Readiness] #showing values in percentages
VAR count_delimiter =
LEN ( Unit_Readiness ) - LEN ( SUBSTITUTE ( Unit_Readiness, ",", "" ) )
#in the case when there are two values delimited with ",". Because conditional formatting #works on numbers only, we have to split the string (e.g. 100%, 99%) and check if they meet #conditions and assign a flag to them
VAR last_Unit_Readiness_position =
IF (
count_delimiter = 0,
BLANK (),
FIND ( "@", SUBSTITUTE ( Unit_Readiness, ",", "@", count_delimiter ) )
)
VAR last_Unit_Readiness_len =
IF (
count_delimiter = 0,
BLANK (),
LEN ( Unit_Readiness ) - last_Unit_Readiness_position
)
VAR last_Unit_Readiness =
VALUE (
SWITCH (
TRUE (),
ISERROR ( SEARCH ( "%", Unit_Readiness ) ), BLANK (),
count_delimiter = 0, LEFT ( Unit_Readiness, LEN ( Unit_Readiness ) - 1 ),
LEFT (
RIGHT ( Unit_Readiness, last_Unit_Readiness_len ),
last_Unit_Readiness_len - 1
)
)
) / 100
VAR upcoming_gates = [Upcoming Gate] # the [Unit Readiness] shows the readiness of the #particular gate
VAR count_delimiter_gate =
LEN ( upcoming_gates ) - LEN ( SUBSTITUTE ( upcoming_gates, ",", "" ) )
VAR last_gate_position =
IF (
count_delimiter_gate = 0,
BLANK (),
FIND ( "@", SUBSTITUTE ( upcoming_gates, ",", "@", count_delimiter_gate ) )
)
VAR last_gate_len =
IF (
count_delimiter_gate = 0,
BLANK (),
LEN ( upcoming_gates ) - last_gate_position
)
VAR last_gate =
SWITCH (
TRUE (),
count_delimiter_gate = 0, upcoming_gates,
LEFT ( RIGHT ( upcoming_gates, last_gate_len ), last_gate_len )
)
VAR min_p_calc =
CALCULATE (
SELECTEDVALUE ( 'Unit Readiness Table'[Y_min] ), #[Y_min] is a min. the threshold value for readiness not to be red
'Unit Readiness Table'[P Gate] = TRIM ( last_gate ) # [P Gate] is a gate's name
)
VAR min_m_calc =
CALCULATE (
SELECTEDVALUE ( 'Unit Readiness Table M'[Y_min] ),
'Unit Readiness Table M'[M Gate] = TRIM ( last_gate )
)
VAR y_min_P =
IF (
SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "PMP",
min_p_calc,
min_m_calc
)
VAR max_p_calc =
CALCULATE (
SELECTEDVALUE ( 'Unit Readiness Table'[Y_max] ),# Y_max is the threshold to be green color
'Unit Readiness Table'[P Gate] = TRIM ( last_gate )
)
VAR max_m_calc =
CALCULATE (
SELECTEDVALUE ( 'Unit Readiness Table M'[Y_max] ),
'Unit Readiness Table M'[M Gate] = TRIM ( last_gate )
)
VAR y_max_P =
IF (
SELECTEDVALUE ( 'All Project v2'[Project Type] ) = "P",
max_p_calc,
max_m_calc
)
VAR overall_status =
SWITCH (
TRUE (),
OR (
TRIM ( last_gate )
IN {
"Start In",
"Start De",
"Start Proto",
"Insufficient Data",
"No Next Gate"
},
ISBLANK ( last_Unit_Readiness )
), BLANK (),
last_Unit_Readiness < y_min_P, "FlagLow",
last_Unit_Readiness > y_max_P, "FlagHigh",
"FlagMedium"
)
RETURN
overall_status
Solved! Go to Solution.
Hi @Anonymous ,
Sorry for the late reply. You can change the visualization of the table to disable interaction with the chart.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
A little confused, is this you want?
If not, please provide the output you want.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So... Is there a way to do this?...
Absolutely! You see, whenever I am clicking on a donut chart's section, it filters the table, but not to the degree I want
Your example is what I want, yes
Hi @Anonymous ,
Sorry for the late reply. You can change the visualization of the table to disable interaction with the chart.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |