cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Resolver II
Resolver II

Seperate then Calculate Numeric Values in an Alpha Numeric Column

This is a multiple question post. Below is an image of the desired result - layout can change if necessary.

SAMPLE

Patient IDService Date

PIEN3&4

(Session Loc)

PIEN6

(Session Min)

Hr Conversion
HS-66611/5/2021Home1803
 11/6/2021Home601
 11/7/2021Home601
Total  3005
AM-457411/2/2021Home1202
 11/3/2021Home1202
 11/4/2021Home601
Total  3005
MW-324810/28/2021Home601
 11/1/2021Home1803
 11/2/2021Home1803
Total  4207

Below are the columns in my table (name Observations

Patient IDFull NameOBS NameDescriptionOBS ValueCPT codeService Date
  PIEN3PIE Note 3HomeNB-FAM 
  PIEN4PIE Note 4HomeNB-FAM 
  PIEN6PIE Note 661NB-FAM 

As seen, the OBS Value table holds alpha and numeric values.

Task 1 - create a tabular or matrix that somewhat resembles the SAMPLE above where the PIEN 3&4 display in one column and the PIEN 6 which is actually the minuts of the session, display in a second column.

Task 2 - once the minutes are seperated from the location (and other data in that column) I need to convert the minuts to hours

Task 3 - Display a Total Hours AND a Total Sessions (based on service date)

 

Please be sure to clarify if I should add a new column or a measure as I struggle to look at DAX and know the difference.

 

Thanks in advance - Ashley

 

1 ACCEPTED SOLUTION
anwilkins
Resolver II
Resolver II

4 REPLIES 4
anwilkins
Resolver II
Resolver II

Resolved

lbendlin
Super User
Super User

I've stared at your table for a good ten minutes and still cannot understand the structure.  Power BI works best with a traditional table structure with rows and columns. Somehow you will need to unpivot your original format.

 

Maybe you can explain it again?  Where does NB-FAM come from? 

Hi, and thank you! the NB-FAM is a CPT Code that represents Non Billable Family Services. We use this code when a grant is paying for the service oppossed to an insurance plan. 

 

Here is a pic of the MERGE_Observations table filtered for the data I want

anwilkins_0-1639397931723.png

This is what it looks like with no filters in place, You can see the NAME, DESCRIPTION, OBSValue and CPT code columns hold lots of unnecessary information for this report

anwilkins_1-1639398434712.png

anwilkins_2-1639398461434.png

Hope this helps

Ashley

 

 

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.