Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ReadTheIron
Helper III
Helper III

Matrix for Dates after Latest Date

I have two tables, one for failures and one for repairs.

 

ReadTheIron_0-1650982439889.png

ReadTheIron_1-1650982481881.png

They're not related to each other, but they're both related to AllAssets, a list of all unique assets.

ReadTheIron_3-1650983285186.png

I want to create a matrix that shows all the failure dates after the latest repair date. I can make one that shows the latest repair date and all the failure dates by putting AllAssets and FailureDate in Rows and Latest RepairDate in Values, but I only want to show the FailureDates that appear after Latest RepairDate. Something like this:

 

ReadTheIron_2-1650983116713.png

 

Not really sure where to start with this one! Any help appreciated.

1 ACCEPTED SOLUTION
moizsherwani
Continued Contributor
Continued Contributor

@ReadTheIron You could use a measure as a filter (visual fitler) on the table as below. I have attached the file as an example to get you started

 

moizsherwani_0-1650984801223.png

FailureDateAfterRepairDate =
VAR MaxRepairDate =
    CALCULATE ( MAX ( Repair[RepairDate] ), ALLEXCEPT ( Repair, AllAssets[Asset] ) )
VAR CurrentFailureDate =
    SELECTEDVALUE ( Failure[FailureDate] )
RETURN
    IF ( CurrentFailureDate > MaxRepairDate, 1, BLANK () )

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

View solution in original post

2 REPLIES 2
ReadTheIron
Helper III
Helper III

Thanks, this put me right on track! Do you know if there's any way to display the Latest RepairDate as a row in a matrix visual? I can add it as a value, but then it repeats in every line.

ReadTheIron_0-1650987311745.png

I've tried writing a measure for the max repair date, but when I include it on the row level it breaks the visual.

moizsherwani
Continued Contributor
Continued Contributor

@ReadTheIron You could use a measure as a filter (visual fitler) on the table as below. I have attached the file as an example to get you started

 

moizsherwani_0-1650984801223.png

FailureDateAfterRepairDate =
VAR MaxRepairDate =
    CALCULATE ( MAX ( Repair[RepairDate] ), ALLEXCEPT ( Repair, AllAssets[Asset] ) )
VAR CurrentFailureDate =
    SELECTEDVALUE ( Failure[FailureDate] )
RETURN
    IF ( CurrentFailureDate > MaxRepairDate, 1, BLANK () )

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.