DAX Commands to populate a column from a piece of another column and display the counts

[Strongbuck]

I want to create a visual that can be filter by priority.  The priority data, unfortunaley, currently resides in the checklist title field.

1. I need to parse the priority from the checklist title into a column named "Priority".  The priority column already exists in the table.

2. Add a visual (table) that displays the number of priority 0, 1, 2 and 3 checklists with percentage that are not started (0), in process (>0 and <100) and number that are complete (100).

The raw data looks like this.

CHECKLIST TITLE	Priority	Percentage completed
Door inspection of EX.4.1 - Priority 1		100
Door inspection of EX.2.1 - Priority 1		0
Door inspection of SP.2350 - Priority 3		0
Door inspection of EX.33.21		50
Door inspection of 23.393IN - Priority 2		75

Step 1:

I need to populate the priority column using the checklist title priority value.  If the checklist title does not have a priority, I want to populate the priority field with zero.

CHECKLIST TITLE	Priority	Percentage completed
Door inspection of EX.4.1 - Priority 1	1	100
Door inspection of EX.2.1 - Priority 1	1	0
Door inspection of SP.2350 - Priority 3	3	0
Door inspection of EX.33.21	0	50
Door inspection of 23.393IN - Priority 2	2	75

In SQL, I would use an update statement and set the Priority column using a like command.  How do I do this in DAX?

Step 2:

I want to use a visual to see how many priority 0, 1, 2 and 3 checklist have not been started, in-process and completed.  The visual would display like this:

Priority	Not started	In-Process	Complete
0	0	1	0
1	1	0	1
2	0	1	0
3	1	0	0

I am on day two of trying to learn Power BI and DAX.  Any help would be appriecated!

[Anonymous · Accepted solution]

Hi @Strongbuck ,

Step 1:

New a calculated column:

Priority = 
VAR _loc =
    FIND ( "Priority", 'Table'[CHECKLIST TITLE],, 0 )
VAR _len =
    LEN ( 'Table'[CHECKLIST TITLE] )
VAR _len2 =
    LEN ( "Priority" )
VAR _num =
    IF (
        _loc = 0,
        0,
        VALUE ( RIGHT ( 'Table'[CHECKLIST TITLE], _len - _loc - _len2 ) )
    )
RETURN
    _num

Step 2:

New 3 measures:

Not started = CALCULATE(COUNTROWS('Table'),'Table'[Percentage completed]=0) + 0

In-Process = CALCULATE(COUNTROWS('Table'),'Table'[Percentage completed]>0&&'Table'[Percentage completed]<100) + 0

Complete = CALCULATE(COUNTROWS('Table'),'Table'[Percentage completed]=100) + 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Strongbuck]

Well, it has been hours and no one responded to my question.  I found directions for the first step so I figured I would document it for the next person that is new to Power BI.  I followed the directions I found at

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-add-custom-column

Screen where you can add criteria to parse one columns data into a brand new column.