Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |