Forum Discussion

user900's avatar
user900
Helper II
2 years ago
Solved

Matrix help when date filter is applied

Here is a sample of my data.  It shows only 2 students of many:

I have a measure to calculate the number of times a student failed a test, cumulating each month

**bleep**.Fail Count =

VAR StartDate = DATE(2023,5,1)
VAR CurrentDate = 'TestRecord'[Report Date]
VAR CurrentStudentID = 'TestRecord'[Student ID]
RETURN
CALCULATE(
    COUNTA('TestRecord'[Student ID],
    FILTER(
        ALL('TestRecord'),
        'TestRecord'[Report Date]<=CurrentDate &&
        'TestRecord'[Report Date]>=StartDate &&
        'TestRecord'[Student ID]=CurrentStudentID &&
        'TestRecord[Fail]="True"
    ))
 
and a column to show if the student failed more than once
Repeat Fail? = 'Test Record'[Last]>"1" (last represents the maximum **bleep**.Fail Count per student)
 
In this sample, I expect to see Total Repeat Fails = 2.  My card shows 2, so this works.
But my matrix does not.  It needs to include Student Name, the tests they failed and Fail result.  Here is my expected result:

I also have a slicer for Professor.  When I select George, my matrix shows many more students and should only show 1. 

 

When I add a visual filter for the last report date, it shows 1 name as expected, but it also filters out the previous test history.  I need to have the history.  I also tried to change the visual filter to show all dates.  This brings back the history, but not the correct number of students that failed more than once.  Any ideas on how to fix this?

 

Thanks in advance,

~user 900

 
 
 
 
 
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    user900 , In such case it always better to use date table joined with date of your tbale

     

     

    example

    VAR StartDate = DATE(2023,5,1)
    VAR CurrentDate = 'Date'[Date]
    RETURN
    CALCULATE(
    COUNTA('TestRecord'[Student ID],
    FILTER(
    ALL('Date'),
    'Date'[Report Date]<=CurrentDate &&
    'Date'[Report Date]>=StartDate ),
    Filter('TestRecord', 'TestRecord' [Fail]="True")
    ))

     

     

    then filter should work properly

     

    You can also explore the window function

     

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    user900 , In such case it always better to use date table joined with date of your tbale

     

     

    example

    VAR StartDate = DATE(2023,5,1)
    VAR CurrentDate = 'Date'[Date]
    RETURN
    CALCULATE(
    COUNTA('TestRecord'[Student ID],
    FILTER(
    ALL('Date'),
    'Date'[Report Date]<=CurrentDate &&
    'Date'[Report Date]>=StartDate ),
    Filter('TestRecord', 'TestRecord' [Fail]="True")
    ))

     

     

    then filter should work properly

     

    You can also explore the window function

     

    Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f