Forum Discussion
Conditional Formatting based on a specific rule
- 3 years ago
Then maybe this is what you are looking for?
Highlight different in all other projects = IF ( HASONEVALUE ( 'Table'[Part Number] ), VAR _FirstProject = MIN ( 'Table'[ProjectName] ) RETURN IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Table'[ProjectName] ) ), ALLEXCEPT ( 'Table', 'Table'[Part Number] ) ) > 1, VAR _PhysicalPartNumbersCurrentProject = CALCULATETABLE ( VALUES ( 'Table'[Part Physical Number] ), 'Table'[ProjectName] = _FirstProject, ALL ( 'Table'[Part Physical Number] ) ) VAR _PhysicalPartNumbersOtherProjects = CALCULATETABLE ( VALUES ( 'Table'[Part Physical Number] ), 'Table'[ProjectName] <> _FirstProject, ALL ( 'Table'[Part Physical Number] ) ) VAR _DifferentPartNumbers = EXCEPT ( _PhysicalPartNumbersOtherProjects, _PhysicalPartNumbersCurrentProject ) VAR _CurrentProjectOnlyPartNumbers = EXCEPT ( _PhysicalPartNumbersCurrentProject, _PhysicalPartNumbersOtherProjects ) RETURN IF ( COUNTROWS ( _DifferentPartNumbers ) + COUNTROWS ( _CurrentProjectOnlyPartNumbers ) > 0, "orange" ) ) )Highlight different physical part numbers in other projects
- 3 years ago
Your download link doesn't work anymore, but according to your data model, this should work:
Highlight different in all other projects = CALCULATE( IF ( HASONEVALUE ( 'Parts'[Part Number] ), VAR _FirstProject = MIN ( 'Parts'[ProjectName] ) RETURN IF ( CALCULATE ( COUNTROWS ( VALUES ( 'Parts'[ProjectName] ) ), ALLEXCEPT ( 'Parts', 'Parts'[Part Number] ) ) > 1, VAR _PhysicalPartNumbersCurrentProject = CALCULATETABLE ( VALUES ( 'Parts'[Part Physical Number] ), 'Parts'[ProjectName] = _FirstProject, ALL ( 'Parts'[Part Physical Number] ) ) VAR _PhysicalPartNumbersOtherProjects = CALCULATETABLE ( VALUES ( 'Parts'[Part Physical Number] ), 'Parts'[ProjectName] <> _FirstProject, ALL ( 'Parts'[Part Physical Number] ) ) VAR _DifferentPartNumbers = EXCEPT ( _PhysicalPartNumbersOtherProjects, _PhysicalPartNumbersCurrentProject ) VAR _CurrentProjectOnlyPartNumbers = EXCEPT ( _PhysicalPartNumbersCurrentProject, _PhysicalPartNumbersOtherProjects ) RETURN IF ( COUNTROWS ( _DifferentPartNumbers ) + COUNTROWS ( _CurrentProjectOnlyPartNumbers ) > 0, "orange" ) ) ), ALL ( 'slicers'[project_code] ), ALL ( 'pj_code'[project_code] ) )I'd love to see your Power BI guidelines 😉
It is correct, finally. But what a bummer - when I showed it to stakeholders, they said that they want to see it as a column rather than a conditional formatting feature 🤣
Now when I use it as a column, because of
ALL ( 'slicers'[project_code] )It turns off the filter from the whole table visual
This nightmare never ends
Time to really question hard your Power BI guidelines. The problem ist not the measure. The problem is that you define different tables for visual specific use. In a recommended star schema as described in the linked manual there would never be such thing like a "slicer" table (named by usage in the visual). Instead there would be a "Project" table (named by the business object). Now, how to fix it (as much as you can fix such solution): In the table visual, also use the project from the slicers table, not from the Parts table.