Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Formatting based on a specific rule

Hey everyone! I need help with the following issue (the data in the sample pbix): Some Part Numbers belong to different projects:       And, as you can see, the physical number is...
@Conditional Formatting
@Dax
  • Martin_D's avatar
    Martin_D
    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

  • Martin_D's avatar
    Martin_D
    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 😉

Martin_D's avatar
Martin_D
Solution Sage
3 years ago

You added two more tables listing the projects and used one of the new tables as a slicer, instead of the existing column 'Tabel'[ProjectName] (or now: 'Parts'[ProjectName]). If you want to compare in DAX anything in the selected context (here: Part numbers in selected projects from the slicer) against anything in the all(anything) context (here: Part numbers in all projects), then you need to explicitly include all(anything) (here: all projects) in the DAX code. This piece of code always refers only to the specific columns that are mentionend in the code (here: 'Table'[ProjectName], or now: 'Parts'[ProjectName]), but not automatically to any new synonym tables and columns that you introduced. That means: although the DAX measure removes the filter from 'Parts'[ProjectName], the filter from the new field 'slicers'[project_code] still applies when the measure is looking for duplicate part numbers (only within projects selected in 'slicers'[project_code]). If you add new columns to the model that you want to use as slicers/filters/categories in visuals then you also need to add them to the code (here:  'pj_code'[project_code], 'slicers'[project_code]). So if you want to reduce the required code changes, don't change your data model too often. The good news is: You don't need to change the code, you just need to replace the field used in the slicer with 'Parts'[ProjectName], then it works.

A best practice approach is to:

  • first do the dimensional star schema data modeling (i.e. define the tables and relationships)
  • then define which fields are to be used in slicers/filters/as categories in visuals and hide all synonym fields, in order to prevent that they are used in the report, because your DAX code needs to deal with each field that you use in the report explicitly. Usually the fields used in slicers/filters/as categories in visuals come from the dimension table. Example: You have a ProjectPartMappingTable and a Project dimension table with a distinct list of projects. Then you would hide the Project field in the mapping table an only use the Project field from the dimension table in your report.
  • then create the measures accordingly

Further reading: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Also, I am just an intern with a lot of limitations and clear set of instructions to work with the given😪