Filter Matrix Table by Sum of Measure by Row

Question

Hi,&nbsp;&nbsp;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.&nbsp; Please see below for a screenshot.&nbsp;&nbsp;Any assistance would be greatly appreciated!!&nbsp;&nbsp;Here is the link to a sample file:&nbsp;https://drive.google.com/file/d/1WYTL5gsA7qs6GQ0c2sMBYxYQJPJdGOAI/view?usp=sharing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

mahoneypat · Answer

I would encourage you to not use Auto Date/Time in your file (uncheck that in Options) and make a separate Date table.&nbsp; The automatically generated Date table contains all dates from your min to max date.&nbsp; To get it working with your current file, i had to do two things
&nbsp;
1.&nbsp; Add a calculated column to get the YearMonth of just your actuals NoteDates with

MonthNoteDate = FORMAT(ProgressNotes[NoteDate], "YYYYMM")
&nbsp;
2. Make a new measure to be used in your visual level filter (not added to visual with this measure expression), filtered to &gt;= 3.
&nbsp;

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

&nbsp;
Creating a simple date table in DAX - SQLBI
&nbsp;
Pat
&nbsp;

awalsh · Answer

Hi mahoneypat&nbsp; - thanks for your help!&nbsp;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 -&nbsp;&nbsp;Count of Months AND Count of Member ID&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;Is there a formula that would account for filtering both the MemberID and Count of Months with specific conditions for each?&nbsp;&nbsp;Thank you so much for your help&nbsp;

mahoneypat · Answer

Try this measure expression as your visual level filter with "is 1".
&nbsp;
CountofMonthFilter&nbsp;w&nbsp;Members&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DISTINCTCOUNT&nbsp;(&nbsp;ProgressNotes[MonthNoteDate]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;ProgressNotes,&nbsp;ProgressNotes[CM&nbsp;Name]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&gt;=&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;COUNT&nbsp;(&nbsp;ProgressNotes[MemberID]&nbsp;)&nbsp;&gt;=&nbsp;4,&nbsp;&nbsp;&nbsp;&nbsp;1)
&nbsp;
Pat
&nbsp;

anonymous · Answer

Hi&nbsp;awalsh&nbsp;,
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
                &amp;&amp; 'ProgressNotes'[NoteDate].[Month] = _curmonth
        )
    )
VAR _ncountofmember =
    COUNTROWS (
        GROUPBY (
            FILTER ( 'ProgressNotes', _countofmember &gt;= 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 &gt;= 3 )
    )Count of CM = SUMX ( GROUPBY ( 'ProgressNotes', 'ProgressNotes'[CM Name] ), [Measure] )

Best Regards

Forum Discussion

Filter Matrix Table by Sum of Measure by Row

4 Replies

Related content

Matrix Row Filtering Alters Rolling DATESINPERIOD Measures

Filter Matrix by dynamic measure without recalculating values (Hierarchy Matrix - Power BI)

Temporary table with filters, measure in matrix

Matrix table filtered by measure ?

Dynamic measure filtering