Filtering Visual to only show Accounts with two Criteria

[mramstead1]

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!

[mramstead1]

Thank you! I didn't post the file due to client data existing within it but I will try and make sample data in the future.

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

[KNP · Accepted solution]

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
    )