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 

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