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 

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