Forum Discussion
Filtering Visual to only show Accounts with two Criteria
Hello,
I am trying to throw together a quick matrix to identify accounts that may have switched their billing method. Below is a snapshot of the matrix I have:
The goal is to create a measure that I could throw onto the visual as a filter that would keep any accounts that have both Agency and Direct BillModes while removing the others that only include one. I've tried different various different DAX formulas to try and solve this to no avail.
For extra clarity (in case it matters), each Account will have anywhere from 1-50+ lines that get combined into either Direct or Agency BillMode.
Any help on this is greatly appreciated!
It's a little difficult to be certain with the info provided, can I suggest you post some sample data next time that closely represents your actual data.
Something like this may work. It may need an additional step to SUMMARIZE the mode if I'm misunderstanding the data.
Let me know if you have questions.
Filter = VAR vfilter = FILTER ( SUMMARIZE ( 'Table', 'Table'[Account], "cr", COUNT ( 'Table'[Account] ) > 1 ), [cr] = TRUE () ) VAR vAccounts = SUMMARIZE ( vfilter, 'Table'[Account] ) RETURN IF ( MIN ( 'Table'[Account] ) IN vAccounts, 1 )
3 Replies
- KNP
Super User
It's a little difficult to be certain with the info provided, can I suggest you post some sample data next time that closely represents your actual data.
Something like this may work. It may need an additional step to SUMMARIZE the mode if I'm misunderstanding the data.
Let me know if you have questions.
Filter = VAR vfilter = FILTER ( SUMMARIZE ( 'Table', 'Table'[Account], "cr", COUNT ( 'Table'[Account] ) > 1 ), [cr] = TRUE () ) VAR vAccounts = SUMMARIZE ( vfilter, 'Table'[Account] ) RETURN IF ( MIN ( 'Table'[Account] ) IN vAccounts, 1 ) - Ashish_Mathur
Hi,
So in the visual that you have shared, only the first and last accounts should appear in the result. Am i correct? If yes, then share the download link of the PBI file.
