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

@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 😉

15 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Martin_D
        Solution Sage
        3 years ago

        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?