Forum Discussion

awalsh's avatar
awalsh
Icon for Helper I rankHelper I
5 years ago

Filter Matrix Table by Sum of Measure by Row

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 le...
Need Help
Tips and Tricks
Tutorial Requests
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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]))
 
 
Pat
 
awalsh's avatar
awalsh
Icon for Helper I rankHelper I
5 years ago

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 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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