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

Lookup values in 21 different columns to derive a measure

Hello world,

 

I have a set of 21 parameters(Performance Indicators), based on which I want to indentify high risk assets. The excel below is a sub set of actual data which contains 21 parameters and 374 assets. Each asset is deisgnated a score based on predefined criteria which is the Performance Indicator(PI). The Score starts at 1 which is a low risk and goes all the way upto 5 which is a high risk. We wanted to build a measure which can help me identify assets which need my immediate attention. the measure should essentially check the following,

 

 Any 1 PI score of 5

            or

 Any 3 PI score of 4

            or

 Any 7 PI score of 7

 

For instance "Alpha Asia" has a score of 5 in the COC column, so it automatically needs my attention. However, if it had a score of 4 in any of the 16 variable, it would still need my attention or a score of 3 in any seven PI.

 

I hope someone here can help me with a measure to help me identify high risk assets. 

 

Thanks in advance,

 

WIth best regards,

 

Kryptonian 

 

Update:

@v-piga-msft I have 21 paramters in my orginal data set, out of which I am using 13 here in the data. the parameters in the excel are as follows:

  1. Ageing
  2. Emp Score
  3. COC
  4. Critical M/C Failure
  5. Insurance Rank
  6. CAT Score
  7. Near Miss
  8. Overdue Invoices
  9. OverDue Relief
  10. PSC Score
  11. Resthour Violations
  12. Safety EQ Failure
  13. Insurance Claims

Asset code column is a code assigned to my assets in the orgnization. Agsint each asset, i see the score of a paramter. The score ranges from 1 to 5. Score of 1 being a low score(good) and 5 being a high risk(extremely bad). 

For me to prioritize my focus,  I need to focus on assets

1 score of 5 in any of the 13 columns

or

3 score of 4 in any of the 13 columns

or

7 score of 3 in any of the 13 columns

 

I need to prepare some sort of measure, which indentifies the any asset which falls in the above criteria. 

 

Please let me know if it need further clarifications

 

 

 

Asset CodeAgeingEmp ScoreCOCCritical M/C FailureInsurance RankCAT ScoreNear MissOverdue InvoicesOverDue ReliefPSC ScoreResthour ViolationsSafety EQ FailureInsurance Claims
Alpha-Asia2251111214211
Alpha-Sea 5151111211111
Alpha-Ore 5151111414211
Alpha-Yuan5151111512211
Alpha-Winc5151112414211
Alpha-Mand5151411311114
Alpha-Nere5151511411115
Alpha-Ana 5151511411215
Alpha-Parn5151511524515
Alpha-Inte5251511414215
Alpha-Eos5151521411115
Alpha-Tese5151521411215
Alpha-Hero5251521414115
Alpha-Gene5231111511211
Alpha-Kemp5131121411411
Alpha-Ming5131121511111
Alpha-Shan5231121314311
Alpha-MSC 5121413414214
Alpha-Mine5121511511215
Alpha-Anto5121511511355
Alpha-Gott5121511514115
Alpha-MSC 5121512511215
Alpha-Maer5221111511211
Alpha-ROSC5221111514111
Alpha-Koku5221121514111
Alpha-Inte5221531414215
Alpha-Al G1111111111111
Alpha-Al R1111111111111
Alpha-Al S1111111111111
1 ACCEPTED SOLUTION

 

Occam's razor (also Ockham's razor or Ocham's razorLatin: lex parsimoniae "Law of Parsimony") is the problem-solving principle that the simplest solution tends to be the right one. When presented with competing hypotheses to solve a problem, one should select the solution with the fewest assumptions. 

 

I managed to find a solution to problem. Thankfully, there were not many variable to deal with. In the below table I created an additonal column for each variable. It was a calculated column, which checks if the variable value is 4, if yes then 1 else 0. Then I simply added all the calculated columns and check if the sum was greater than or equal to 3. 

 

View solution in original post

4 REPLIES 4

 

Occam's razor (also Ockham's razor or Ocham's razorLatin: lex parsimoniae "Law of Parsimony") is the problem-solving principle that the simplest solution tends to be the right one. When presented with competing hypotheses to solve a problem, one should select the solution with the fewest assumptions. 

 

I managed to find a solution to problem. Thankfully, there were not many variable to deal with. In the below table I created an additonal column for each variable. It was a calculated column, which checks if the variable value is 4, if yes then 1 else 0. Then I simply added all the calculated columns and check if the sum was greater than or equal to 3. 

 

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @bikram_laishram,

 

I'm still a little confused about your sceanrio, what is the 21 parameters?

 

In addition, could you share your desired output so that we can help further on it?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft I have 21 paramters in my orginal data set, out of which I am using 13 here in the data. the parameters in the excel are as follows:

  1. Ageing
  2. Emp Score
  3. COC
  4. Critical M/C Failure
  5. Insurance Rank
  6. CAT Score
  7. Near Miss
  8. Overdue Invoices
  9. OverDue Relief
  10. PSC Score
  11. Resthour Violations
  12. Safety EQ Failure
  13. Insurance Claims

Asset code column is a code assigned to my assets in the orgnization. Agsint each asset, i see the score of a paramter. The score ranges from 1 to 5. Score of 1 being a low score(good) and 5 being a high risk(extremely bad). 

For me to prioritize my focus,  I need to focus on assets

1 score of 5 in any of the 13 columns

or

3 score of 4 in any of the 13 columns

or

7 score of 3 in any of the 13 columns

 

I need to prepare some sort of measure, which indentifies the any asset which falls in the above criteria. 

 

Please let me know if it need further clarifications

@v-piga-msft Hope you have had some breakthrough man. I have googled to get results but not able to get this one

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.