Filtering a matrix column by another column or by TopN

[ReadTheIron]

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 Reported is after Remediation, and the first Date Reported before Remediation. Failing that, I would like to display the rows with the three latest Date Reported. I've been playing around with TopN but haven't gotten anywhere. Can anyone point me in the right direction? Many thanks.

[ReadTheIron]

@amitchandak, @HashamNiaz , thank you for the help! Unfortunately I run into the same issue when I try either solution - I can't write a measure or a DAX expression that references two tables. Date Reported comes from one table (call it FailureTable), all of the other fields from another (call it AssetTable). The two tables are related many-to-one on Common Name.

So if I try creating a measure, I can get as far as =countrows(filter(FailureTable,FailureTable[Date Reported], but then I can't enter AssetTable[Remediation].

If I try adding a calculated column to AssetTable, I can't enter FailureTable.

[Anonymous]

Hi @ReadTheIron,

In fact, DAX expressions can be calculated or invoked across multiple tables.

Power BI DAX How to Summarize Data From Multiple Tables - Plainly Blog - Data Modelling, Advanced An...

Can you please share some dummy data that keep the raw data structure and expected results to paste here with table format? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[ReadTheIron]

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

AssetTable

Common Name	Remediation	Date of Last Failure	Latest Failure Date After Remediation
BMT-J2-291A	6/27/2021	7/24/2021	7/24/2021
IRT-L1-203	5/22/2021	8/31/2021	8/31/2021
BMT-Q1-471	7/17/2021	7/14/2021

And FailureTable

Common Name	Date Reported	Cause Code
IRT-L1-203	8/31/2021	No Cause Found
BMT-J2-291A	7/24/2021	No Applicable Cause Code Found
BMT-Q1-471	7/14/2021	No Cause Found
BMT-Q1-471	7/5/2021	No Cause Found
IRT-L1-203	6/21/2021	Debris
BMT-J2-291A	6/2/2021	No Cause Found
BMT-Q1-471	3/27/2021	Worn
BMT-J2-291A	3/25/2021	Grounded
BMT-J2-291A	3/18/2021	Grounded
IRT-L1-203	3/18/2021	Out of adjustment
IRT-L1-203	2/24/2021	No Cause Found
BMT-J2-291A	2/8/2021	No Cause Found
BMT-J2-291A	2/1/2021	Weather
BMT-Q1-471	12/17/2020	Out of adjustment
BMT-J2-291A	12/16/2020	No Cause Found
IRT-L1-203	10/7/2019	No Cause Found
IRT-L1-203	10/5/2019	Age
BMT-J2-291A	1/3/2019	Age

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 · Accepted solution]

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]

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.

[HashamNiaz]

Hi @ReadTheIron !

To solve this issue, you first needs to create a column which calculates either [Date Reported] is greater than [Remediation] or not. You can create calculated column for that purpose using below DAX;

IsDateReport = IF(Table[DateReported] > Table[Remediation], 1, 0)

After this you will need to create a Ranking measure, which will Rank based your column selection like (Common Name, Remediation), this will create Ranking, now you can use this measure to filter out only Top 3 Ranks.

Regards,

Hasham

[amitchandak]

@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)