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

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.

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    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.