Conditional Formatting based on a specific rule

[Anonymous]

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 the same, meaning that this is one part they are using.

Some of the Part Numbers belong to different projects, but have different Physical Parts:

My desired outcome: measure (or anything) that:

1) Calculates whether a Part Number belongs to different projects

2) If it belongs, check whether it has the same Physical Number

3) If it does not have the same Physical Number, it highlights Part Number color orange

(I have no access to power query or anything, just DAX)

Sample file 

[Martin_D · Accepted solution]

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]

Use this measure to highlight Part Physical for parts that have multiple physical parts associated:

Highlight multiple =
CALCULATE (
IF (
AND (
HASONEVALUE ('Table'[Part Number] ),
DISTINCTCOUNT ( 'Table'[Part Physical] ) > 1
),
"orange"
),
ALL ( 'Table'[Part Physical] )
)

Use this measure to highlight Part Physical with different number than part:

Highlight different = IF ( SELECTEDVALUE ( 'Table'[Part Number] ) <> SELECTEDVALUE ( 'Table'[Part Physical] ), "orange" )

Use this measure to highlight Part Physical if any or both of the cases above are valid:

Highlight both =
IF (
OR (
CALCULATE (
AND (
HASONEVALUE ('Table'[Part Number] ),
DISTINCTCOUNT ( 'Table'[Part Physical] ) > 1
),
ALL ( 'Table'[Part Physical] )
),
SELECTEDVALUE ( 'Table'[Part Number] ) <> SELECTEDVALUE ( 'Table'[Part Physical] )
),
"orange"
)

Then use conditinal field based cell background formatting:

Conditional cell background formatting

[Anonymous]

Okay, now the problem is that projects that are part number and part physical are assigned to prevent the measure to work properly. Is it because of the filter context?

[Martin_D]

What do you refer to as "projects"? Your data model knows only Table.Part Number and Table.Part Physical. Would you mind posting your latest file?

[Anonymous]

I am so sorry for the confusion, here are the details and update pbix:
Some Part Numbers belong to different projects:

- 

And, as you can see, the physical number is the same, meaning that this is one part they are using.

Some of the Part Numbers belong to different projects, but have different Physical Parts:

My desired outcome: measure (or anything) that:

1) Calculates whether a Part Number belongs to different projects

2) If it belongs, check whether it has the same Physical Number

3) If it does not have the same Physical Number, it highlights Part Number color orange

UPDATED LINK 

[Martin_D]

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]

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 · Accepted solution]

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]

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]

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]

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

[Anonymous]

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.

This is the problem, the slicer must come from the slicers[project_code], it's like a rule (but not mine) that I cannot break😥

[Martin_D · Accepted solution]

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]

I forgot that you renamed 'Table' as 'Parts', pls. check the latest code.

[Anonymous]

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

[Martin_D]

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.