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
Strongbuck
Helper I
Helper I

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

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

Door inspection of EX.4.1 - Priority 1

1100

Door inspection of EX.2.1 - Priority 1

10

Door inspection of SP.2350 - Priority 3

30

Door inspection of EX.33.21

050

Door inspection of 23.393IN - Priority 2

275

 

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:

 

PriorityNot startedIn-ProcessComplete
0010
1101
2010
3100

 

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

vcgaomsft_0-1664440781116.png

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

vcgaomsft_1-1664440891086.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

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

vcgaomsft_0-1664440781116.png

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

vcgaomsft_1-1664440891086.png

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

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.Screen where you can add criteria to parse one columns data into a brand new column.

 

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.

Top Solution Authors