Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 ID Service Date PIEN3&4(Session Loc) PIEN6(Session Min) Hr Conversion HS-666 11/5/2021 Home 180 3 11/6/2021 Home 60 1 11/7/2021 Home 60 1 Total 300 5 AM-4574 11/2/2021 Home 120 2 11/3/2021 Home 120 2 11/4/2021 Home 60 1 Total 300 5 MW-3248 10/28/2021 Home 60 1 11/1/2021 Home 180 3 11/2/2021 Home 180 3 Total 420 7

Below are the columns in my table (name Observations

 Patient ID Full Name OBS Name Description OBS Value CPT code Service Date PIEN3 PIE Note 3 Home NB-FAM PIEN4 PIE Note 4 Home NB-FAM PIEN6 PIE Note 6 61 NB-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
Resolver II

Resolved

4 REPLIES 4
Resolver II

Resolved

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?

Resolver II

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

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

Hope this helps

Ashley

Super User

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors