Forum Discussion
Visual Interactions Vs Filters in DAX
Hi,
Say, I have a dashboard with slicer/filters for Business Unit (BU), Department, Account, Reporting Manager, Revenue Type and Dates.
I have matrix Visuals with PM EMail ID in rows, and few KPIs as values. I make each cell done with conditional formatting of green, amber or red.
Dates comes from calendar table.
Business Unit (BU), Department, Account comes from dim table - Master - Meta Data; and
PM Email ID, Reporting Manager, Revenue type comes from fact table vwu_process_compliance_data. All my KPIs measures created are based on data from vwu_process_compliance_data.
there is active relationship between the 3 said tables.
Based on User selection of BU, Dept or Account, the list of PMs changes along with values for each of the KPIs. All these are fine.
I use user defined function to determine the Red Amber Green. Basically for given KPI measure, it takes count of all the PMs for who we have data as denominator and then ranks the given PM's score. Based on what percentile the Red, Amber and Green is decided.
This is fine.
The screenshot as follows:
Help Required:
The ask now is to compare the PM against the entire PM population in Business Unit, irrespective of dept or account selected, OR when the user can change to compare and rank PM based on selected filters. I have a Guage Scope as a disconnected Table showing two values BU level / Applied filters.
I have a DAX to count the PMs based on Guage Scope, if BU level, it should count all PMs if Applied filters it should count PMs within the applied filtered data. I have added this count of PMs also in Matrix as values for validation purpose. However, as my Matrix is set with Interactivity with slicer filters my remove filters in DAX measure is not getting applied. I am thinking when I use this in UDF, it may affect my denominator to show only by filtered data.
How to have this fixed?
Refer to my AllValidPMs DAX Measure. For more clarity I have 3 cards with measures as follows:
1. Card titled TotalPMsAfterFilter has interaction on with Account filter, shows count as 79 PM total
DAX:
TotalPMsAfterFilter = COUNTROWS(VALUES(vwu_process_compliance_data[PM Email Id]))
2. Card titled AllValidPMs has interaction ON with Account filter, shows count as 78 PM total with no blanks for selected KPI (Schedule Adherence)
3. Card titled AllValidPMs has interaction OFF with Account filter, shows count as 248 PM total with no blanks for selected KPI (Schedule Adherence)
DAX used in AllValidePMs are as follows:
AllValidPMs =
VAR GScope = SELECTEDVALUE( 'Gauge Scope'[Scope], "BU level" )
VAR IsKPIBlank = ISBLANK( [Schedule Adherence] )
/* BU-only scope: respect BU + Date, ignore Dept/Account (dim) and RM/RevenueType (fact) */
VAR ValidPMs_BU =
FILTER (
CALCULATETABLE (
ALL ( vwu_process_compliance_data[PM Email Id] ),
/* Fact filters off (Reporting Manager, Revenue Type, etc.) */
REMOVEFILTERS ( vwu_process_compliance_data ),
/* Remove only Dept & Account from the dimension; keep BU intact */
REMOVEFILTERS ( 'Master - Meta Data'[Department SF] ),
REMOVEFILTERS ( 'Master - Meta Data'[Account (Standardized)] )
/* Calendar filters remain applied */
),
NOT ISBLANK ( [Schedule Adherence] )
)
/* All-filters scope: keep all slicers as-is (BU/Dept/Account/RM/RevenueType + Date) */
VAR ValidPMs_AllFilters =
FILTER (
ALL ( vwu_process_compliance_data[PM Email Id] ),
NOT ISBLANK ( [Schedule Adherence] )
)
VAR TotalPMsCnt =
IF ( GScope = "BU level", COUNTROWS(ValidPMs_BU), COUNTROWS(ValidPMs_AllFilters ))
RETURN
TotalPMsCnt
/* This is to rank PMs, will comment this section for time being. You can ignore as well.
VAR Rank_PM =
IF(GScope = "BU level",
IF (
UPPER ( TRIM ( Direction ) ) = "LTB", -- Lower The Better
RANKX ( ValidPMs_BU, KPIName, , ASC, DENSE ),
RANKX ( ValidPMs_BU, KPIName, , DESC, DENSE ) -- Higher The Better (GTB default)
),
IF (
UPPER ( TRIM ( Direction ) ) = "LTB", -- Lower The Better
RANKX ( ValidPMs_AllFilters, KPIName, , ASC, DENSE ),
RANKX ( ValidPMs_AllFilters, KPIName, , DESC, DENSE ) -- Higher The Better (GTB default)
)
)
/* Pick pool based on slicer */
VAR PercentilePosition = DIVIDE ( Rank_PM, TotalPMsCnt )
/* Find the first RAG band whose cumulative percentile >= this position */
VAR Band =
CALCULATETABLE (
TOPN ( 1, RAGTbl, RAGTbl[CummPerc], ASC ),
RAGTbl[CummPerc] >= PercentilePosition
)
/* --- Return hex color --- */
SWITCH (
TRUE (),
IsKPIBlank, "#808080", -- grey for blank KPI
TotalPMsCnt < 4, "#808080", -- grey when too few PMs to compare
MAXX ( Band, RAGTbl[HexColor] )
)
*/
Hi prasaddn , Thank you for reaching out to the Microsoft Community Forum.
Yes, this is fundamentally a model issue, not a DAX issue. As long as PM exists only as part of a composite ID (PM + Account + Department), there is no real concept of a “PM at BU level”, so no amount of REMOVEFILTERS or ALL will ever give a correct population. PM must be a standalone dimension (one row per PM, with its own key) and the fact table should reference it via a proper key instead of a concatenated business ID. Once that is done, your original logic for BU-level vs filtered ranking will work naturally without complex DAX.
6 Replies
- FBergamaschiSuper User
Hi prasaddn
a picture of the data model qould help, you described it but visually it is easier to see it
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
- prasaddnFrequent Visitor
Hi FBergamaschi ,
The model is quite huge and may confuse outsider.. I am sharing the limited view.
the vwu_process_compliance_data is connected with Master - Meta Data using ID column, which is concatenation :
ID = CONCATENATE(vwu_process_compliance_data[Sub Department New],CONCATENATE(vwu_process_compliance_data[Account STD],vwu_process_compliance_data[PM Email Id]))And, I have the Master - Meta data a union table as follows:
Master - Meta Data =
DISTINCT (
UNION (
SELECTCOLUMNS (
SUMMARIZE (
Initiation,
Initiation[Sub department New],
Initiation[ID],
Initiation[Account STD],
Initiation[PM STD]
),
"SubDept Name", Initiation[Sub department New],
"ID", Initiation[ID],
"Account_Std", Initiation[Account STD],
"PM Name", Initiation[PM STD]
),
SELECTCOLUMNS (
SUMMARIZE (
vwu_process_compliance_data,
vwu_process_compliance_data[Sub Department New],
vwu_process_compliance_data[ID],
vwu_process_compliance_data[Account STD],
vwu_process_compliance_data[PM STD]
),
"SubDept Name", vwu_process_compliance_data[Sub Department New],
"ID", vwu_process_compliance_data[ID],
"Account_Std", vwu_process_compliance_data[Account STD],
"PM Name", vwu_process_compliance_data[PM STD]
),
SELECTCOLUMNS (
SUMMARIZE (
hr_master,
hr_master[Sub Department New],
hr_master[ID],
hr_master[Account_Std],
hr_master[PM STD]
),
"SubDept Name", hr_master[Sub Department New],
"ID", hr_master[ID],
"Account_Std", hr_master[Account_Std],
"PM Name", hr_master[PM STD]
))
)
- prasaddnFrequent Visitor
On further research I found the issue is with my model design and bridge tables used, like the ID column created. this is causing none of the remove() and keepfilters() work effectively. I will try to get my model fixed. Meanwhile if any other suggestions, please let me know.
- v-hashadapuCommunity Support
Hi prasaddn , Thank you for reaching out to the Microsoft Community Forum.
Yes, this is fundamentally a model issue, not a DAX issue. As long as PM exists only as part of a composite ID (PM + Account + Department), there is no real concept of a “PM at BU level”, so no amount of REMOVEFILTERS or ALL will ever give a correct population. PM must be a standalone dimension (one row per PM, with its own key) and the fact table should reference it via a proper key instead of a concatenated business ID. Once that is done, your original logic for BU-level vs filtered ranking will work naturally without complex DAX.
- v-hashadapuCommunity Support
Hi prasaddn , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
- v-hashadapuCommunity Support
Hi prasaddn , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.