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

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