topic Calculate measure by re-filtering table / taking value of column as applying to another column in DAX Commands and Tips

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

acekalina — Thu, 24 Sep 2020 15:00:28 GMT

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?

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

dedelman_clng — Thu, 24 Sep 2020 18:09:48 GMT

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

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

acekalina — Thu, 24 Sep 2020 19:49:39 GMT

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.

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

dedelman_clng — Thu, 24 Sep 2020 20:00:52 GMT

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.

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

acekalina — Fri, 25 Sep 2020 06:28:23 GMT

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!