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 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.

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 

6 Replies

  • TomMartens's avatar
    TomMartens
    Super 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.

       

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    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