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

Then probably this is what you are looking for:

Highlight different and in project = 
IF (
    HASONEVALUE ( 'Table'[Part Number] ),
    IF (
        AND (
            COUNTROWS ( VALUES ('Table'[ProjectName] ) ) > 0,
            COUNTROWS ( FILTER ( 'Table', SELECTEDVALUE ( 'Table'[Part Number] ) <> SELECTEDVALUE ( 'Table'[Part Physical Number] ) ) ) > 0
        ),
        "orange"
    )
)

 

Highlight part number if physical part number is different in project context

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Almost there, but there are a couple of things:

1) If the part number and physical number are the same - we live them as they are, I don't need them colored

2) If the Part number and Physical part are different - it is okay, as long as they are the same in a project context:

- You can see that the projects are different, but the part numbers are the same, and the physical parts are the same, which means I don't need them colored 

 

3) I need to color Part Numbers that: are the same in different projects but have different physical parts:

 

Here you can see that the projects are different, part numbers are the same, but the physical parts are different. In this case, I need Part Number 1410566 to be colored orange

 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hey there! Continuing this thread because the issue is related to this question.

       

      sample (updated) 

      The problem I currently have with the main pbix (the data modek presented below reflects it, but the values, of course, are replaced):

      - project_code of the slicers table is supposed to be used, well, as a slicer:

      - if a project is selected via the project_code slicer, the table visual should be filtered accordingly. Below we can see that project 933 has those Part Numbers with different Physical Parts because Part Numbers are highlighted (NO SLICER APPLIED):

      - But when I use the slicer on the right to get me project 933, Part Numbers are not highlighted anymore:

       

      Basically, this is the main problem. I want those part numbers to be highlighted even when I select the project from the project_code of the slicers table.

       

      (the data model should not be changed, and the slicer must be this project_code of the slicers table)

      • 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