cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Help developing a DAX Measure

I am trying to create a measure that tracks issues with payment records. The records contain three key peices of information opportunity stage, commitment stage, and payment stage. If an opportunity is withdrawn the commitment and payments need to be withdrawn. I've been able to solve this very basically using the code below (as you can tell my DAX knowledge is limited but am very eager to learn how to solve this).

 

Logic for Closed Opps w/ Open Commtiments and/or Payments = 
    IF('Payment'[Commitment.Stage] = "Withdrawn",
        AND('Payment'[Stage] = "Withdrawn", 1)
    )

I am now running into issues where there are multiple payments associated with opportunities where some have been sent and returned. What I need assistance with is identifying how to exclude these scenarions where multiple payments have been sent where there are sent and returned funds where there are no unallocated funds to be disbursed. 

 

I've provided the link below with test data:

https://www.dropbox.com/s/0f1h05s3pt5y4i4/Test%20File%20for%20PBI%20Comm.pbix?dl=0

 

Any assistance is greatly appreciated!

1 ACCEPTED SOLUTION

So I was playing around with your PBI file to try and get the syntax right, and as far as I can tell, there are only 2 Commitments in the table that need to be hidden by the 2nd set of conditions, but are shown by the first. However, both of these commitments (C-0221 & C-0660) are hidden by your page filter (Opportunity Stage = Declined or Withdrawn) because their parent Opportunity Stage is Complete.  

Were you meaning to hide some of the entries in your current table, or are they all good, and this is just an enhancement for future possibilities?

Anyway, here's what I used as another calculated column to filter on to find that out:

Logic for Payment info = 
    'Payment'[Commitment.Unallocated Funds] = 0 &&
    COUNTROWS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) && Payment[Stage]="Sent")) > 1 &&
    CONTAINS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) ), Payment[Stage], "Returned")

The first thing to keep in mind is that calculated columns evaluate each row separately. So the first condition is easy: Make sure that the [Commit.Unallocated Funds] on the current row is 0. 
The second condition is the trickiest one.  It counts the rows returned by the filter statement.  The filter statement take the Payment table, and only returns rows where the Commitment ID is the same as the Commitment ID in the EARLIER context.  That context (from before we started filtering the whole table) is the current row we're evaluating.  It then filters the table further, only returning rows where the Payment[Stage] is "Sent".  Now that the count is complete, it checks if the count total is more than one.

Finally, the third condition.  I could have written this in the same style as the previous one, with a >0 condition instead.  However, I wanted to expose you to different ways of finding the same information.  So instead of counting rows, this expression will get the Payment table, filter it down to where CommitmentID is the same as the earlier context, and see if the [Stage] column contains the value "Returned".

 

A lot of parts, but each part breaks down very nicely. I'm happy to answer any questions you may have, and break this down even farther if you need it.

 

View solution in original post

8 REPLIES 8
Cmcmahan
Resident Rockstar
Resident Rockstar

This is a neat way to set up page level filters!  To really help you, I've got a few questions about how this is set up.

In your logic measure, are you just trying to test for two conditions?  You can clean that up like this:

 

Logic for Closed Opps w/ Open Commtiments and/or Payments2 = 
    IF('Payment'[Commitment.Stage] = "Withdrawn" && 'Payment'[Stage] = "Withdrawn",
        TRUE()
    )

Or clean it up even more:

Logic for Closed Opps w/ Open Commtiments and/or Payments3 = 
    'Payment'[Commitment.Stage] = "Withdrawn" && 'Payment'[Stage] = "Withdrawn"

 

 

I'm also noticing a LOT of data replication, especially in the Payments table, where a lot of the values are just being copied from the parent commitment.  This is likely not an issue because your dataset is only ~10,000 rows, but could be de-duplicated and normalized since you have relationships set up correctly.



Anyway, moving on to your actual issue.  I need help defining your expected results.  Correct me if I get any of this wrong. 

  • You want to hide/remove commitments from the matrix in certain cases.
  • The entire commitment should not be shown if the unallocated funds equal zero AND there is more than one associated payment with Stage = Sent AND there is at least one payment with Stage = Returned associated with the commitment

Is this correct?  If so, you can set up a measure using multiple conditions like above.  First checking if unallocated funds is zero, then checking that the count of payments in the Sent stage is greater than 1, and then checking if the stage column contains the value Returned.   

If you're still unsure how to proceed, I can help with creating this new measure and applying it to your data.

Anonymous
Not applicable

Thanks for the reply @Cmcmahan!

 

Currently my formula is only testing for two conditions. When I originally wrote the formula I wasn't certain if I would run into any other issues like I am now so it needs to be updated to account for multiple conditions.

 

You are correct, my end goal is to hide items commitments that do not meet the conditions set. Below are the conditions where a commitment should not be shown.

 

  • A commitment stage equals withdrawn and a payment stage equals withdrawn
  • The unallocated funds equal zero and there is more than one associated payment with Stage = Sent AND there is at least one payment with Stage = Returned associated with the commitment

Since one commitment can have multiple payments I am uncertain how to write a formula that looks at the commitment and identifies multiple payments then reviews the payments to check if they meet the payment stage requirements.  

 

Any additional assistance is greatly appreciated!

So I was playing around with your PBI file to try and get the syntax right, and as far as I can tell, there are only 2 Commitments in the table that need to be hidden by the 2nd set of conditions, but are shown by the first. However, both of these commitments (C-0221 & C-0660) are hidden by your page filter (Opportunity Stage = Declined or Withdrawn) because their parent Opportunity Stage is Complete.  

Were you meaning to hide some of the entries in your current table, or are they all good, and this is just an enhancement for future possibilities?

Anyway, here's what I used as another calculated column to filter on to find that out:

Logic for Payment info = 
    'Payment'[Commitment.Unallocated Funds] = 0 &&
    COUNTROWS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) && Payment[Stage]="Sent")) > 1 &&
    CONTAINS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) ), Payment[Stage], "Returned")

The first thing to keep in mind is that calculated columns evaluate each row separately. So the first condition is easy: Make sure that the [Commit.Unallocated Funds] on the current row is 0. 
The second condition is the trickiest one.  It counts the rows returned by the filter statement.  The filter statement take the Payment table, and only returns rows where the Commitment ID is the same as the Commitment ID in the EARLIER context.  That context (from before we started filtering the whole table) is the current row we're evaluating.  It then filters the table further, only returning rows where the Payment[Stage] is "Sent".  Now that the count is complete, it checks if the count total is more than one.

Finally, the third condition.  I could have written this in the same style as the previous one, with a >0 condition instead.  However, I wanted to expose you to different ways of finding the same information.  So instead of counting rows, this expression will get the Payment table, filter it down to where CommitmentID is the same as the earlier context, and see if the [Stage] column contains the value "Returned".

 

A lot of parts, but each part breaks down very nicely. I'm happy to answer any questions you may have, and break this down even farther if you need it.

 

Anonymous
Not applicable

Thanks @Cmcmahan ! There are some scenarios that are purposefully excluded as they are captured in different reports. When I entered your forumla I did not quite achieve the desired goal but am very close. How would you go about filtering out items where the commitment stage is equal to withdrawn and all the associated payments are withdrawn?

 

Below is a link to the updated dropbox file.

 

https://www.dropbox.com/s/0f1h05s3pt5y4i4/Test%20File%20for%20PBI%20Comm.pbix?dl=0

 

Sure.  In order to do page filtering, you have to create a DAX calculated column.  In this case, I added it to the Commitment table, since it meant to  filter the commitment in specific cases:

Commitment & All Payments Withdrawn = Commitment[Stage] = "Withdrawn" && 
CALCULATE(SELECTEDVALUE(Payment[Stage]), FILTER(Payment, Payment[Commitment ID] = EARLIER(Commitment[Commitment Id]))) = "Withdrawn"

In this case, I'm using a weird quirk of SELECTEDVALUE.  It turns out that if you have multiple results in the column called by SELECTEDVALUE, if they're all the same then you get that value out, if not you get a default blank.  So I'm checking first that the Commitment[Stage] is "Withdrawn" and then filtering the Payment table where commitment ID is the same as the current Commitment ID and if all of the Payment[Stage] of that table are the same and equal to "Withdrawn" then this results in True.

Add this as a page level filter, and select only the False results, and you're there!

https://drive.google.com/open?id=1I6v-F2p7ZGgzJEVruBJUQ3yppJBhHv4Y

Cmcmahan
Resident Rockstar
Resident Rockstar

There are also other ways to accomplish the above calculation to get the same results, that you may want to use in other scenarios.  If you're trying to catch multiple payment stages (say you wanted commitments with both "Withdrawn" and "Unallocated" payments) you could instead use something like this instead of the SELECTEDVALUE above

0 <> COUNTROWS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Commitment[Commitment Id]) && Payment[Stage]<>"Withdrawn" && Payment[Stage]<>"Unallocated" ))


This is filtering rows out where payment stage is Withdrawn or Unallocated from payments and then counting them.  If this counts any rows, then that means there are payments NOT in Withdrawn/Unallocated, and then you should display that entire commitment.

Just showing that with DAX there are always multiple ways to skin the cat.  You can also use CONTAINS to try and determine if the filtered table of payments has certain values, or IFHASONEVALUE to determine if there's only one type of payment.

Anonymous
Not applicable

Thank you for the very rich explanations! I'm not quite sure why there are still some records showing when they have one payment as sent and one as returned. It's clear it is filtering some out but why not all of them? 

Anonymous
Not applicable

I was able to get this to work by combining these into a switch statement to run through multiple conditions.

 

Column = 
SWITCH(TRUE(),
COUNTROWS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) && Payment[Stage]= "Sent")) >= 1 && COUNTROWS(FILTER(Payment, Payment[Commitment ID] = EARLIER(Payment[Commitment ID]) &&Payment[Stage] = "Returned")) >= 1, "False", 
'Payment'[Commitment.Stage] = "Withdrawn" && 'Payment'[Stage] = "Withdrawn", "False")

Thanks again @Cmcmahan !

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!