cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leharkapil Member
Member

Highlight Changes in different versions(columns) in Matrix

Hi,

 

I have a car manufacturing dataset which looks like this

 

Config IDFeature GroupFeature SubgroupPart NoPart Desc
12345 DCA1BasicBasic Machine1Basic Machine,Chasis
12345 DCA1DashboardAC Vent2AC Parts, Plastic, AC Gas
12345 DCA1DashboardSteering3Leather, Ring, Buttons
12345 DCA1DashboardIndicator4Plastic,Connectors
12345 DCA1TyreRubber5Tyre,Puncture Liquid
12345 DCA2BasicBasic Machine1Basic Machine,Chasis
12345 DCA2DashboardAC Vent2AC Parts, Plastic, AC Gas
12345 DCA2DashboardSteering3Leather, Ring, Buttons
12345 DCA2DashboardIndicator4Plastic,Connectors
12345 DCA2TyreRubber5Tyre,Puncture Liquid
12345 DCA2OtherLights6Headlamps,Tail-lamps,Sidelamps
12345 DCA3BasicBasic Machine1Basic Machine,Chasis
12345 DCA3DashboardAC Vent2AC Parts, Plastic, Eco Gas
12345 DCA3DashboardSteering3Leather, Ring, Buttons
12345 DCA3DashboardIndicator4Plastic,Connectors
12345 DCA3TyreRubber5Tyre,Puncture Liquid
12345 DCA3OtherLights6Headlamps,Tail-lamps,Sidelamps

 

Out of the above dataset, I have created a Power BI Matrix as below : 

 

Lineage.png

 

The only thing I want to do here is 

Highlight the changes between different  Config IDs.

Eg: from 12345 DCA1 to 12345 DCA2(see highlighted text which was not there in 12345 DCA1 but added in 12345DCA2)

And then highlight the change between 12345 DCA2 and 12345DCA3(AC Parts,Plastic,Eco Gas which was a modification)

 

*This is a part of dynamic dataset so data will change based on the filter selection

Any help or workaround would be helpful...

1 ACCEPTED SOLUTION

Accepted Solutions
TeigeGao Super Contributor
Super Contributor

Re: Highlight Changes in different versions(columns) in Matrix

Measure =
VAR previous_version =
    CALCULATE (
        MIN ( Table1[Part Desc] ),
The following part, we will get the data of previous Config ID in the same Feature Subgroup FILTER ( ALL ( Table1 ), Table1[Feature Subgroup] = MIN ( Table1[Feature Subgroup] ) && Table1[Config ID]
This part use CONCATENATE() function to get previous Config ID = CONCATENATE ( "12345 DCA", VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1 ) ) ) RETURN
This part will check if the previous value is same as the current value IF ( previous_version <> MIN ( Table1[Part Desc] ) && VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1 >= 1, 1, 0 )
5 REPLIES 5
TeigeGao Super Contributor
Super Contributor

Re: Highlight Changes in different versions(columns) in Matrix

Hi @leharkapil ,

In this scenario, we can create a measure to judge that and add it to conditional formatting. We can use the following DAX query to create a measure:

Measure =
VAR previous_version =
    CALCULATE (
        MIN ( Table1[Part Desc] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Feature Subgroup] = MIN ( Table1[Feature Subgroup] )
                && Table1[Config ID]
                    = CONCATENATE (
                        "12345 DCA",
                        VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1
                    )
        )
    )
RETURN
    IF (
        previous_version <> MIN ( Table1[Part Desc] )
            && VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1 >= 1,
        1,
        0
    )

Please pay attention to that this query only works when the actual data is like the sample data, especially the Config ID because I use the CONCATENATE ("12345 DCA",VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1).

After that we can add the measure to conditional formatting:

PBIDesktop_whA4dktIbm.png

The result will like below:

PBIDesktop_vAbtTlmZv1.png

Best Regards,

Teige

leharkapil Member
Member

Re: Highlight Changes in different versions(columns) in Matrix

Hi Tiege,

 

Thanks for your reply.

Config ID value can be anything apart from the one mentioned in your DAX.

Also could you please explain parts of the code?

 

 

TeigeGao Super Contributor
Super Contributor

Re: Highlight Changes in different versions(columns) in Matrix

Measure =
VAR previous_version =
    CALCULATE (
        MIN ( Table1[Part Desc] ),
The following part, we will get the data of previous Config ID in the same Feature Subgroup FILTER ( ALL ( Table1 ), Table1[Feature Subgroup] = MIN ( Table1[Feature Subgroup] ) && Table1[Config ID]
This part use CONCATENATE() function to get previous Config ID = CONCATENATE ( "12345 DCA", VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1 ) ) ) RETURN
This part will check if the previous value is same as the current value IF ( previous_version <> MIN ( Table1[Part Desc] ) && VALUE ( RIGHT ( MIN ( Table1[Config ID] ), 1 ) ) - 1 >= 1, 1, 0 )
leharkapil Member
Member

Re: Highlight Changes in different versions(columns) in Matrix

Thanks a lot. How can I turn this into a dynamic version without hardcoding

TeigeGao Super Contributor
Super Contributor

Re: Highlight Changes in different versions(columns) in Matrix

This depends on your environment, generally, it cannot change dynamic if we can't find the law of these id.