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

[acekalina]

Hi,

Could you please help with the following problem I have faced?

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?

[dedelman_clng]

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

[acekalina]

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.

[dedelman_clng · Accepted solution]

---

@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.

[acekalina]

@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!