Forum Discussion
Helper IIIFiltering a matrix column by another column or by TopN
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
Helper IIIamitchandak, 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.
Hi ReadTheIron,
In fact, DAX expressions can be calculated or invoked across multiple tables.
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!
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