Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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:

PreacherBaby_0-1687504907264.png

 

PreacherBaby_1-1687504907268.png

 

 

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:

PreacherBaby_2-1687504924824.png

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 

2 ACCEPTED SOLUTIONS

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 projectsHighlight different physical part numbers in other projects

View solution in original post

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 😉

View solution in original post

15 REPLIES 15
Martin_D
Super User
Super User

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 formattingConditional cell background formatting

Anonymous
Not applicable

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?

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
Not applicable

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

PreacherBaby_0-1687503963852.png

PreacherBaby_1-1687504043935.png

 

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:

PreacherBaby_2-1687504185664.pngPreacherBaby_3-1687504247803.pngPreacherBaby_4-1687504274297.png



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 

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 contextHighlight part number if physical part number is different in project context

Anonymous
Not applicable

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

PreacherBaby_0-1687512701821.png

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

PreacherBaby_1-1687512800522.png

- 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:

PreacherBaby_3-1687513086501.png

 

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

 

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 projectsHighlight different physical part numbers in other projects

Anonymous
Not applicable

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:

PreacherBaby_0-1687769839291.png

- 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):

PreacherBaby_1-1687770315634.png

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

PreacherBaby_2-1687770411804.png

 

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)

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
Not applicable

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

Anonymous
Not applicable

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😥

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 😉

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.