Create KPI Step - Extract text from multiple fields and create new column with 1 or 0

[Anonymous]

Hi,

Could you please help me?

I would like to create an indicator, to show the progress of each purchase step in the report.

I am a beginner in DAX, researched posts and could not find something similar to my case due to the type of field I have.

I have a List in Sharepoint Online = "Project" with a single field "Step_Puchase" type Checkboxes (allow multiple selections)

 field Step_Purchase:

‘1. Develop RFP
‘2. Process Supplies
‘3. SAP Contract Signature
‘4. PO Emission
‘5. Kick Off Supplie

I imagined creating 05 KPI columns, and checking if in the Spep_Purchase field, there is the text "1. Develop" would assign a value of 1 in KPI01, if there is a text "2. Process" would assign a value of 1 in KPI02, and so on.

In PowerBI later I worked on these KPIs with conditional formatting with the colors of the icons. Using the KPI Measure formula Spep_Purchase = unichar (11044)

I tried to create the 05 columns, but only left to create 01, the others have an error stating that a column with multiple values needs a counter.

New Column KPI01_Purchase = SWITCH(
TRUE(),
SEARCH( "Develop", Projects[Step_Purchase], 1, 0 ) > 0, "1"
)

New Column KPI02_Purchase = SWITCH(
TRUE(),
SEARCH( "Process", Projects[Step_Purchase], 1, 0 ) > 0, "1"
)

New Column KPI03_Purchase = SWITCH(
TRUE(),
SEARCH( "Contract", Projects[Step_Purchase], 1, 0 ) > 0, "1"
)

Best regards.
Mauricio

[MFelix · Accepted solution]

Hi @Anonymous ,

I would do the following in order to not create new columns:

• Create a table with the steps (disconnected)
• Create the following measure:

Step value= IF(SEARCH(SELECTEDVALUE(Steps[Step]), SELECTEDVALUE('Table'[Step]),1, 0) > 0 , 1, blank())

Now do the condittional formatting based on this measure:

PBIX file attach.

[Anonymous]

Hi @MFelix

I was very happy with your instruction.

Unfortunately I was unable to open your PBIX file, its version is more recent than mine. I depend on the Service Desk team to update (without permission).

Based on your instruction, I was able to achieve the desired result. (I hope I did it right :))
- Created a Steps table (1 column, 05 rows for the steps)
- I created 01 new measure for each Step. Total of 05 measures

Measure with my real values:
1.Develop RFP = IF (SEARCH (SELECTEDVALUE (Step_Purshase [Steps], "1.Develop RFP"), SELECTEDVALUE ('Projects' [Step_Progress_Purchase]), 1, 0)> 0, 1, blank ())
2.Process Supplies = IF (SEARCH (SELECTEDVALUE (Step_Purshase [Steps], "2.Process Supplies"), SELECTEDVALUE ('Projects' [Step_Progress_Purchase]), 1, 0)> 0, 1, blank ())
...

As soon as I can open your PBIX file, I’ll see if I’ve done your explanation.
Thank you very much.

[MFelix]

Hi @Anonymous,

There was no need to create the 5 measure using a single measure with the code below you can use a single measure.

Kpi = IF (SEARCH (SELECTEDVALUE (Step_Purshase [Steps], SELECTEDVALUE ('Projects' [Step_Progress_Purchase]), 1, 0)> 0, 1, blank ())

Using this single measure all the columns are filled 

But glad you were abble to adjust it.