Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 s...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
MFelix
Super User
5 years ago

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.