Forum Discussion

brlimagu's avatar
brlimagu
Frequent Visitor
8 years ago
Solved

Filtering data with different dates

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 fai...
Need Help
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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 

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
8 years ago

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's avatar
    brlimagu
    Frequent Visitor
    8 years ago

    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.