cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
doobie Regular Visitor
Regular Visitor

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

Accepted Solutions
Cmcmahan New Contributor
New Contributor

Re: Help developing a DAX Measure

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.

 

8 REPLIES 8
Cmcmahan New Contributor
New Contributor

Re: Help developing a DAX Measure

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.

doobie Regular Visitor
Regular Visitor

Re: Help developing a DAX Measure

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!

Cmcmahan New Contributor
New Contributor

Re: Help developing a DAX Measure

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.

 

doobie Regular Visitor
Regular Visitor

Re: Help developing a DAX Measure

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

 

Cmcmahan New Contributor
New Contributor

Re: Help developing a DAX Measure

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 New Contributor
New Contributor

Re: Help developing a DAX Measure

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.

doobie Regular Visitor
Regular Visitor

Re: Help developing a DAX Measure

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? 

doobie Regular Visitor
Regular Visitor

Re: Help developing a DAX Measure

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 !