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

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

Reply
acekalina
Frequent Visitor

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

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.

 
rowUser_salesUser_expirationsYear_MonthSalesExpired
1User 1User 12020-051425 
2User 1User 22020-05536 
3User 1User 12020-07 -536
4User 1User 12020-07 458
5User 2User 12019-0586 
6User 2User 12020-05 -1852
7User 2User 12020-05 -86
8User 2User 22018-091312 
9User 2User 22019-05 -1312
10User 2User 32019-051996 
11User 2User 32019-10 1211
12User 3User 12019-10800 
13User 3User 12020-05 -523
14User 3User 12020-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


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

View solution in original post

4 REPLIES 4
dedelman_clng
Community Champion
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 )
    )

 

2020-09-24 13_08_20-Window.png

 

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

 

Hope this helps

David

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.


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

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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