Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Hi @brlimagu,
Share your .pbix file.
Hi,
I think i can help. Please share a sample file to work with.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |