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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

ReadTheIron , one of the ways it to add measure like

 

countrows(filter(Table, [Date Reported] > [Remediation]))

All measure should follow above filter

 

Or create a column and use that as a visual level filter and filter this column =1

if([Date Reported] > [Remediation] ,1,0)