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 😉

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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