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
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
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.