Filtering data with different dates

[brlimagu]

I have a table that shows me all process and subprocess that have failed on a certain day (The user use date slicer to choose the date).

As you can see in the image below, the query shows all failed subprocess and I need to build a report that shows only the last subprocess executed. Is there any way to do it?

Thanks in advance.

[v-qiuyu-msft · Accepted solution]

Hi @brlimagu,

Assume table below: 

In your scenario, you can create a measure below: 

LatestSub = var MaxSubPerID=CALCULATE(MAX('SubProcess'[SbProcess exec_dt]),FILTER('SubProcess','SubProcess'[ProcessID]=MAX('SubProcess'[ProcessID])))
return
CALCULATE(MAX('SubProcess'[Subprocess Name]),FILTER('SubProcess','SubProcess'[SbProcess exec_dt]=MaxSubPerID))

By the way, there are two relationships between Process table and LogProcess table, one Process(1)->LogProcess (N), the other Process(1) ->Log Header-> LogProcess (N). I would suggest you delete the relationship between the Process and Log Header, then change cross filter for relationships Process(1)->LogProcess (N) and Log Header(1)-> LogProcess (N) as Both. For more infomation, see: Create and manage relationships in Power BI Desktop.

Best Regards,
Qiuyun Yu 

[brlimagu]

[Ashish_Mathur]

Hi @brlimagu,

Share your .pbix file.

[Ashish_Mathur]

Hi,

I think i can help.  Please share a sample file to work with.

[TomMartens]

Hey,

you can create a calculated column that marks the latest subprocess by using this DAX statement

Is latest date = 
IF('yourtablename'[Subprocess Exec_dt] = 
CALCULATE(
MAX('yourtablename'[Subprocess Exec_dt])
,ALLEXCEPT('yourtablename', 'yourtablename'[Main Process], 'yourtablename'[Subprocess])
)
,"True"
,"False"
)

Then you can use this column to filter the rows, either by using the column on a slicer, or as filter direct applied to the visual.

Hope this helps

Regards

Tom

[brlimagu]

Hi @TomMartens thank you for your help, but this works for the entire column and I have a Process table with IDs and a SubProcess table with subprocess related to process, I need to show the latest subprocess for each process ID.