Excluding Line-Items that have new "user" Comments, from a curated 'Calculate' measure

[zuro16]

Hello,

I have a loaded table that includes Work-In-Progress amount (“WIP amount”) per line-item ( we call them “Objects” here internally to those line-items..pretty much is a WBS reference from our ERP). I needed to calculate the total WIP that is not included in ‘orderbook’ ( which is another of my tables structured by “Objects” also). Therefore, I went ahead and created the following measures ( with the appropriate relationships) in order to get that “WIP not in orderbook” amount

#1 Measure >> WIP Total = SUM(‘WIP[WIP amount])

#2 Measure >>  WIP not in orderbook = CALCULATE([WIP Total], orderbook[Sales]= 0)

On the other hand, the Objects are available to be updated with specific “Comments” that come via “Power App”, so -here- I have another table ( with a Power App set-up) that also is linked to the other tables by the “Objects” unique identifier herein mentioned, and such table gets updated with the comments that the user inputs ( the comments field has the old-plain regular “text” format).

Which would be the best way now to exclude the items that now have comments, from that “WIP not in orderbook” calculation? I’m not too familiar with the DAX Filter functions or Related function ( or DAX functions/formulas in general to be honest) so I feel I reached a dead -end now and I’m in need to have this new calculation in one of my dashboards.

Many Thanks

Carlos

[parry2k]

@zuro16 glad it worked for you, sorry I missed that NOT part.

Anyhow, you accepted your own reply as a solution which makes sense but you can also accept my reply as a solution. 

[parry2k]

@zuro16I I guess you need this: 

WIP not in OB = CALCULATE([WIP Total], NOT ISBLANK ( 'Comment Table'[Comment] ) )

[zuro16 · Accepted solution]

This is great...thank you @parry2k ...it's actually without the "NOT"....below how I set it up

WIP not in OB = CALCULATE([WIP Total, 'orderbook'[Sales] = 0, ISBLANK('Comment Table'[Comments]))

Screenshot below ( the table on top is the old and the one on the bottom is the one with the new Fx)

I realized since I'm going to have "exceptions comments" and "regular comments", in my second Power App that I am to create  -that one- will have a separate Comments <exceptions> table. Otherwise, all comments ( exceptions and regular) will be excluded from the formula and I only need the "exceptions" comments to be excluded. Many thanks

[parry2k]

@zuro16 why do you have this,, isn't it that should check if there is a comment or not?

WIP not in OB = CALCULATE([WIP Total], 'OB v3[Sales]= 0)

[zuro16]

Yes...good question. The reason is that some line-items will be sort of exceptions, thus , my idea was to have two Power apps > One that will not exclude anything once comments are entered -let's call it- App # 1 ( this is the one that would follow your logic in your question a few instants ago)....and the other one -the one for exceptions- (App #2) that would exclude the line-items from the calculation....so pretty much I'm more interested on App #2 because I already have App #1. Hope this makes sense  : - )

[parry2k]

@zuro16 I would recommend sharing some sample data with the expected output and how your data model looks like, it will help to provide the solution.

[zuro16]

Not sure exactly what to show, but let me try.

1st)  is the table that shows the WIP not on orderbook

2) Then, there is an app set-up via Power Apps that allows user to add comments

3) The tables shown below ( with yellow highlight), are linked by "Object"...the top right table ( Comments_Box_for_WIP) is the table getting updated with the comments that arrive via the Power App.

The idea is that once -somehow- PBI reads there is a comment entered via the Power App or something that identifies that there is a comment now, then that such item gets excluded from the calculation WIP not on orderbook...These are the measures a I created originally

{

#1 Measure >> WIP Total = SUM(‘WIP[WIP amount])

#2 Measure >>  WIP not in OB = CALCULATE([WIP Total], 'OB v3[Sales]= 0)

                                                                                                                          }

In case you need more info, please let me know...thank you