Forum Discussion

ReadTheIron's avatar
ReadTheIron
Icon for Helper III rankHelper III
4 years ago
Solved

Filtering a matrix column by another column or by TopN

I'm presenting data in a matrix that looks like this:     I would like to display all rows where Date Reported is after Remediation. Ideally I would like to display all rows where Date Repor...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI ReadTheIron,

    You can use the following measure formula to check the last 3 report dates which are after 'Remediation Date' and grouped based on the current 'common name', then you can use it on matrix 'visual level filter' to filter records: 

    Flag = 
VAR _currAssetDate =
    CALCULATE (
        MAX ( AssetTable[Remediation] ),
        ALLSELECTED ( FailureTable ),
        VALUES ( AssetTable[Common Name] )
    )
VAR currReportDate =
    MAX ( FailureTable[Date Reported] )
VAR _list =
    CALCULATETABLE (
        VALUES ( FailureTable[Date Reported] ),
        FILTER ( ALL ( FailureTable ), [Date Reported] >= _currAssetDate ),
        VALUES ( FailureTable[Common Name] )
    )
VAR ranked =
    FILTER (
        ADDCOLUMNS ( _list, "Rank", RANKX ( _list, [Date Reported],, DESC ) ),
        [Rank] <= 3
    )
RETURN
    IF ( currReportDate IN SELECTCOLUMNS ( ranked, "Date", [Date Reported] ), currReportDate )


    Regards,

    Xiaoxin Sheng

ReadTheIron's avatar
ReadTheIron
Icon for Helper III rankHelper III
4 years ago

Here's some dummy data that should give the matrix in the original question:

AssetTable

Common NameRemediationDate of Last FailureLatest Failure Date After Remediation
BMT-J2-291A6/27/20217/24/20217/24/2021
IRT-L1-2035/22/20218/31/20218/31/2021
BMT-Q1-4717/17/20217/14/2021 

 

And FailureTable

Common NameDate ReportedCause Code
IRT-L1-2038/31/2021No Cause Found
BMT-J2-291A7/24/2021No Applicable Cause Code Found
BMT-Q1-4717/14/2021No Cause Found
BMT-Q1-4717/5/2021No Cause Found
IRT-L1-2036/21/2021Debris
BMT-J2-291A6/2/2021No Cause Found
BMT-Q1-4713/27/2021Worn
BMT-J2-291A3/25/2021Grounded
BMT-J2-291A3/18/2021Grounded
IRT-L1-2033/18/2021Out of adjustment
IRT-L1-2032/24/2021No Cause Found
BMT-J2-291A2/8/2021No Cause Found
BMT-J2-291A2/1/2021Weather
BMT-Q1-47112/17/2020Out of adjustment
BMT-J2-291A12/16/2020No Cause Found
IRT-L1-20310/7/2019No Cause Found
IRT-L1-20310/5/2019Age
BMT-J2-291A1/3/2019Age

 

Date of Last Failure is a calculated column drawing data from FailureTable.

The matrix is filtered based on whether Latest Failure Date After Remediation is not blank.

 

I'm not sure what I'm doing wrong in trying to create a calculated column referencing both these tables, any help appreciated!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI ReadTheIron,

You can use the following measure formula to check the last 3 report dates which are after 'Remediation Date' and grouped based on the current 'common name', then you can use it on matrix 'visual level filter' to filter records: 

Flag = 
VAR _currAssetDate =
    CALCULATE (
        MAX ( AssetTable[Remediation] ),
        ALLSELECTED ( FailureTable ),
        VALUES ( AssetTable[Common Name] )
    )
VAR currReportDate =
    MAX ( FailureTable[Date Reported] )
VAR _list =
    CALCULATETABLE (
        VALUES ( FailureTable[Date Reported] ),
        FILTER ( ALL ( FailureTable ), [Date Reported] >= _currAssetDate ),
        VALUES ( FailureTable[Common Name] )
    )
VAR ranked =
    FILTER (
        ADDCOLUMNS ( _list, "Rank", RANKX ( _list, [Date Reported],, DESC ) ),
        [Rank] <= 3
    )
RETURN
    IF ( currReportDate IN SELECTCOLUMNS ( ranked, "Date", [Date Reported] ), currReportDate )


Regards,

Xiaoxin Sheng

  • ReadTheIron's avatar
    ReadTheIron
    Icon for Helper III rankHelper III
    4 years ago

    That works beautifully! I'll be studying it to figure out how it works - thank you for sharing your expertise; it's really helping me learn.