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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 )

View solution in original post

6 REPLIES 6
TeigeGao
Solution Sage
Solution Sage

Hi @Anonymous ,

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

Anonymous
Not applicable

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?

 

 

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 )

Hi, Thanks for the solution, even I was looking for this for a long time. May I know why you used -1 there in the measure? what is the use of that?

Anonymous
Not applicable

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.