Filter Matrix Table by Sum of Measure by Row

[awalsh]

Hi, 

I am trying to filter this matrix visual by the "CountofMonth" column (which is a measure) to be greater than or equal to 3 (to only show highlighted rows). However, when I use the visual level filter on this for CountofMonth "to be greater than or equal to 3", it makes the visual go blank.  Please see below for a screenshot. 

Any assistance would be greatly appreciated!! 

Here is the link to a sample file: 

https://drive.google.com/file/d/1WYTL5gsA7qs6GQ0c2sMBYxYQJPJdGOAI/view?usp=sharing

[mahoneypat]

I would encourage you to not use Auto Date/Time in your file (uncheck that in Options) and make a separate Date table.  The automatically generated Date table contains all dates from your min to max date.  To get it working with your current file, i had to do two things

1.  Add a calculated column to get the YearMonth of just your actuals NoteDates with

MonthNoteDate = FORMAT(ProgressNotes[NoteDate], "YYYYMM")

2. Make a new measure to be used in your visual level filter (not added to visual with this measure expression), filtered to >= 3.

CountofMonthFilter = CALCULATE(DISTINCTCOUNT(ProgressNotes[MonthNoteDate]), ALLEXCEPT(ProgressNotes, ProgressNotes[CM Name]))

Creating a simple date table in DAX - SQLBI

Pat

[awalsh]

Hi @mahoneypat  - thanks for your help! 

The formula's are working as expected however, I ran into a problem because I ultimetly need the table to be filtered by 2 conditions - 

Count of Months AND Count of Member ID

For example, I need to show "CM Name with a count of MemberID Greater Than or Equal to 4 for at least 3 months". In the screenshot below, I would need the highlighted area's filtered out since the Count of Member ID each month is less than 4. 

Is there a formula that would account for filtering both the MemberID and Count of Months with specific conditions for each? 

Thank you so much for your help 

[v-yiruan-msft]

Hi @awalsh ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to get the count of members which equal or greater than 4

Count of members(equal or greater than 4) = 
VAR _curcm =
    SELECTEDVALUE ( 'ProgressNotes'[CM Name] )
VAR _curyear =
    SELECTEDVALUE ( 'ProgressNotes'[NoteDate].[Year] )
VAR _curmonth =
    SELECTEDVALUE ( 'ProgressNotes'[NoteDate].[Month] )
VAR _countofmember =
    CALCULATE (
        DISTINCTCOUNT ( 'ProgressNotes'[MemberID] ),
        FILTER (
            'ProgressNotes',
            'ProgressNotes'[NoteDate].[Year] = _curyear
                && 'ProgressNotes'[NoteDate].[Month] = _curmonth
        )
    )
VAR _ncountofmember =
    COUNTROWS (
        GROUPBY (
            FILTER ( 'ProgressNotes', _countofmember >= 4 ),
            'ProgressNotes'[CM Name],
            'ProgressNotes'[NoteDate].[Year],
            ProgressNotes[NoteDate].[Month]
        )
    )
RETURN
    _ncountofmember

2. Create two measures to get the count of CMs that meet the filter conditions

Measure = 
VAR _countofmonth =
    IF (
        HASONEVALUE ( ProgressNotes[NoteDate].[Month] ),
        [Count of members(equal or greater than 4)],
        COUNTX (
            GROUPBY (
                'ProgressNotes',
                'ProgressNotes'[CM Name],
                'ProgressNotes'[NoteDate].[Year],
                ProgressNotes[NoteDate].[Month]
            ),
            [Count of members(equal or greater than 4)]
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'ProgressNotes'[CM Name] ),
        FILTER ( 'ProgressNotes', _countofmonth >= 3 )
    )

Count of CM = SUMX ( GROUPBY ( 'ProgressNotes', 'ProgressNotes'[CM Name] ), [Measure] )

Best Regards

[mahoneypat]

Try this measure expression as your visual level filter with "is 1".

CountofMonthFilter w Members =
IF (
    CALCULATE (
        DISTINCTCOUNT ( ProgressNotes[MonthNoteDate] ),
        ALLEXCEPT ( ProgressNotes, ProgressNotes[CM Name] )
    ) >= 3
        && COUNT ( ProgressNotes[MemberID] ) >= 4,
    1
)

Pat