Forum Discussion

Emanuel's avatar
Emanuel
Icon for Helper I rankHelper I
3 years ago
Solved

Check if value exists in another table based on a specific category

I have 2 tables Problem table and Problem workinfo table and I need the following output:

Problem table Problem work info table OUTPUT
Problem ID Problem IDType Problem IDDoes it have a "Resolution Communication" work info type?
PBI000030128704 PBI000030128704General Information PBI000030128704no
PBI000030134102 PBI000030134102General Information PBI000030134102no
PBI000030138400 PBI000030138400General Information PBI000030138400no
PBI000030138931 PBI000030138931General Information PBI000030138931yes
PBI000030148328 PBI000030138931General Information PBI000030148328no
PBI000030148739 PBI000030138931General Information PBI000030148739no
PBI000030148942 PBI000030138931General Information PBI000030148942no
PBI000030149220 PBI000030138931General Information PBI000030149220no
PBI000030150803 PBI000030138931General Information PBI000030150803yes
  PBI000030138931General Information   
  PBI000030138931Resolution Communications   
  PBI000030148328General Information   
  PBI000030148328General Information   
  PBI000030148739General Information   
  PBI000030148942General Information   
  PBI000030148942General Information   
  PBI000030148942General Information   
  PBI000030148942General Information   
  PBI000030149220General Information   
  PBI000030150803General Information   
  PBI000030150803General Information   
  PBI000030150803General Information   
  PBI000030150803Resolution Communications   

 

There is a Many-to-one cardinality with both ways cross filter active for these tables. 

I am haveing dificuties finding a solution to achiev the above output.

 

Thanks for the help in advance

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi

    Please follow the below steps.

    1. Create a conditional column as a flag.

    2. Now duplicate this table.

    3. Use group by function 

    4 Create conditional column 

     

    Output:

     

    Best Regards,

    Shreya Mukkawar

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Emanuel ,

     

    Approve with Anonymous .

    Here's a way to achieve it via Measure:

    Does it have a "Resolution Communication" work info type? = IF("Resolution Communications" in SELECTCOLUMNS('Problem work info table',"Type",[Type]),"yes","no")

    Final output:

    Best Regards,

    Jianbo Li

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

    • Emanuel's avatar
      Emanuel
      Icon for Helper I rankHelper I
      3 years ago

      Thanks Li for your solution.

      How can I include this measure in a larger calculation?
      So, what I need is to create a measure/column for KPI that has to contain the following logic: If(

      Does it have a "Resolution Communication" work info type?=yes and 
      Report_tgt_date<=Action Date then KPI is met else is not.
      I've added 2 colums to your solution to help with the outcome (I don't know why is not letting me to insert the bi file itself)