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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mpissinati
Regular Visitor

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 @mpissinati ,

 

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 @mpissinati ,

 

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



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 @mpissinati,

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.