Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a car manufacturing dataset which looks like this
Config ID | Feature Group | Feature Subgroup | Part No | Part Desc |
12345 DCA1 | Basic | Basic Machine | 1 | Basic Machine,Chasis |
12345 DCA1 | Dashboard | AC Vent | 2 | AC Parts, Plastic, AC Gas |
12345 DCA1 | Dashboard | Steering | 3 | Leather, Ring, Buttons |
12345 DCA1 | Dashboard | Indicator | 4 | Plastic,Connectors |
12345 DCA1 | Tyre | Rubber | 5 | Tyre,Puncture Liquid |
12345 DCA2 | Basic | Basic Machine | 1 | Basic Machine,Chasis |
12345 DCA2 | Dashboard | AC Vent | 2 | AC Parts, Plastic, AC Gas |
12345 DCA2 | Dashboard | Steering | 3 | Leather, Ring, Buttons |
12345 DCA2 | Dashboard | Indicator | 4 | Plastic,Connectors |
12345 DCA2 | Tyre | Rubber | 5 | Tyre,Puncture Liquid |
12345 DCA2 | Other | Lights | 6 | Headlamps,Tail-lamps,Sidelamps |
12345 DCA3 | Basic | Basic Machine | 1 | Basic Machine,Chasis |
12345 DCA3 | Dashboard | AC Vent | 2 | AC Parts, Plastic, Eco Gas |
12345 DCA3 | Dashboard | Steering | 3 | Leather, Ring, Buttons |
12345 DCA3 | Dashboard | Indicator | 4 | Plastic,Connectors |
12345 DCA3 | Tyre | Rubber | 5 | Tyre,Puncture Liquid |
12345 DCA3 | Other | Lights | 6 | Headlamps,Tail-lamps,Sidelamps |
Out of the above dataset, I have created a Power BI Matrix as below :
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...
Solved! Go to 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 )
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:
The result will like below:
Best Regards,
Teige
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?
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |