cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Calculate measure by re-filtering table / taking value of column as applying to another column

Hi,

I have two measures [Sales] and [Expired], which are simply summing column values. I need a new measure that would recalculated [Expired] measure. Logic is better explained with an example.

When user selects User_sales = User 1, I would like to have [Expired] measure recalculated by removing filter from User_sales column and filtering the same user in User_expirations column.

 row User_sales User_expirations Year_Month Sales Expired 1 User 1 User 1 2020-05 1425 2 User 1 User 2 2020-05 536 3 User 1 User 1 2020-07 -536 4 User 1 User 1 2020-07 458 5 User 2 User 1 2019-05 86 6 User 2 User 1 2020-05 -1852 7 User 2 User 1 2020-05 -86 8 User 2 User 2 2018-09 1312 9 User 2 User 2 2019-05 -1312 10 User 2 User 3 2019-05 1996 11 User 2 User 3 2019-10 1211 12 User 3 User 1 2019-10 800 13 User 3 User 1 2020-05 -523 14 User 3 User 1 2020-05 -800

As a result, for above table, if User_sales = "User 1" is selected, then [Expired_new] measure would sum not only those values in row 3 and 4, but also all all other values next to "User 1" in User_expirations column (marked in blue).

Is it at all possible?

1 ACCEPTED SOLUTION
Community Champion

@acekalina wrote:

Hi @dedelman_clng for:

- User 1 should be -536 + 458 + (-1 852) + (-86) = -2 016

Remove "SUM ( ExSales[Sales] ) +" from the code.

According to your data it should be -536 + 458 + (-1852) + (-86) + (-523) + (-800) = -3339.

``````Expired_New =
VAR __user =
SELECTEDVALUE ( ExSales[User_sales] )
RETURN
CALCULATE (
SUM ( ExSales[Expired] ),
FILTER ( ALL ( ExSales ), ExSales[User_expirations] = __user )
)``````

Also, my PowerBI Desktop does not recognise SELECTEDVALUE function.

For this I'm not sure how to help. You may have typed something wrong. Please share your report file with sensitive data scrubbed if you are still having issues.

4 REPLIES 4
Community Champion

Hi @acekalina  - does this meet your needs?

``````Expired_New =
VAR __user =
SELECTEDVALUE ( ExSales[User_sales] )
RETURN
CALCULATE (
SUM ( ExSales[Sales] ) + SUM ( ExSales[Expired] ),
FILTER ( ALL ( ExSales ), ExSales[User_expirations] = __user )
)
``````

If not, please tell us what values you are expecting for Expired_New

Hope this helps

David

Frequent Visitor

Hi @dedelman_clng for:

- User 1 should be -536 + 458 + (-1 852) + (-86) = -2 016

- User 2 should be -1 312

- User 3 should be 1 211

Basically, Expired_New should show results against User_sales, although they are linked to User_expirations. I do not need to add Sales numbers there, just to see Sales and Expirations against users from one column.

Also, my PowerBI Desktop does not recognise SELECTEDVALUE function.

Community Champion

@acekalina wrote:

Hi @dedelman_clng for:

- User 1 should be -536 + 458 + (-1 852) + (-86) = -2 016

Remove "SUM ( ExSales[Sales] ) +" from the code.

According to your data it should be -536 + 458 + (-1852) + (-86) + (-523) + (-800) = -3339.

``````Expired_New =
VAR __user =
SELECTEDVALUE ( ExSales[User_sales] )
RETURN
CALCULATE (
SUM ( ExSales[Expired] ),
FILTER ( ALL ( ExSales ), ExSales[User_expirations] = __user )
)``````

Also, my PowerBI Desktop does not recognise SELECTEDVALUE function.

For this I'm not sure how to help. You may have typed something wrong. Please share your report file with sensitive data scrubbed if you are still having issues.

Frequent Visitor
@dedelman_clng  I have found that SELECTEDVALUE function is not supported by my connection type (I am connected to data model in SQL Analysis Services). As a workaround I have used FIRSTNONBLANK function, and it works just perfect. Thank you for your help!

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.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors