cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KasperJ90
Helper III
Helper III

Calculated column from table with multiple ranges

Hi,

 

Need your help to add a calculated column based on each category. I have table 1 showing my categories and ranges and then table 2 with the "operation number" to use to sum all minutes within the category range.

 

Table 1:

Category 1: 8000..8099|8200..8999 

Category 2: 2000..7999|9000..9999

 

Table 2:

Operation No.Minutes
85005
899910
250080
9999100

 

Calculated column for category 1 = sum all minutes within category 1 ranges (in this case 15)

Calculated column for category 2 = sum all minutes within category 2 ranges (in this case 180)

 

Thank you in advance!

1 ACCEPTED SOLUTION
OzkanDhont
Resolver II
Resolver II

Hi @KasperJ90 !

 

Do you need it to be a calculated column?

I've created something similar in the past but as a measure which will save up on your data model.

 

Measure = 
CALCULATE(
    SUM('Table (2)'[No.Minutes]),
    FILTER(
        'Table (2)',
            COUNTROWS(
                FILTER(
                    'Table (1)',
                    'Table (2)'[Operations] > 'Table (1)'[Min] &&
                    'Table (2)'[Operations] <= 'Table (1)'[Max]
                )
            )
         )
)

You can download the pbix file for better understanding: 

https://filetransfer.io/data-package/ltw0G1YS#link

 

Hope it helped!

 

Kind regards,

OD

View solution in original post

1 REPLY 1
OzkanDhont
Resolver II
Resolver II

Hi @KasperJ90 !

 

Do you need it to be a calculated column?

I've created something similar in the past but as a measure which will save up on your data model.

 

Measure = 
CALCULATE(
    SUM('Table (2)'[No.Minutes]),
    FILTER(
        'Table (2)',
            COUNTROWS(
                FILTER(
                    'Table (1)',
                    'Table (2)'[Operations] > 'Table (1)'[Min] &&
                    'Table (2)'[Operations] <= 'Table (1)'[Max]
                )
            )
         )
)

You can download the pbix file for better understanding: 

https://filetransfer.io/data-package/ltw0G1YS#link

 

Hope it helped!

 

Kind regards,

OD

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.