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

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
Anonymous
Not applicable

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

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.

 

Capture_TableKPI.PNG

 

 

 

 

 

 

 

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)

 

mpissinati_0-1613430838733.jpeg

 

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

 

MFelix_0-1613580957422.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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:

 

MFelix_0-1613580957422.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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 ())
...
Capture_PBI_HelpStep.PNG

 

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

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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors