Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Highlighting ID's with missing work types

Hello,

 

I have two datasets similar to the below:

 

Dataset 1 - Invoice details

Task IDWork TypeCost
1Erect10
2Dismantle30
3Erect80

 

Dataset 2 - Product Table

Job IDTask ID
341
642
273

 

Relationships created

Dataset 1 - Dataset 2 (Task ID)

 

Dataset 1 is expected to always have a 'Erect' & 'Dismantle' to each 'Task ID' but sometimes this can slip through.

 

I want to create a measure which highlights 'Job ID's' (dataset 2) that either have a missing Erect record or Dismantle record. The measure needs to tell me which record is missing.

 

Can anyone help me achieve this? 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    I add some data：

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _1=SELECTCOLUMNS('Invoice details',"1",[Task ID])
return
IF(
  NOT(  MAX('Product Table'[Task ID])) in _1,"red")

    2. Format – Conditional formatting.

    3. Enter the Background color interface.

    4. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, 

       

      I think there has been some confusion with regards to what I am trying to achieve.

       

      Dataset 1 shows task ID's that have had either an Erect or Dismantle invoice record added to them. Each task ID should have an Erect & Dismantle record but sometimes this can be missed. 

       

      I want to be able to highlight Task ID's that only have an Erect or Dismantle (I.e missing either an Erect or Dismantle)

       

      Looking at the below dataset Task ID 1 & 2 will need to be highlighted as they only have Erect invoice records and are missing the Dismantle.

       

      Dataset 1 - Invoice details

      Task IDWork TypeCost
      1Erect10
      2Erect30
      3Erect80
      3Dismantle40

       

      I hope this clears things up.