Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditionnal Column with a common Key

Hello everyone,   I would like to create a condition that says if from a common key (Claim ID) you have an 2 invoices : the first one with an invoice date before 16/03 and another one after 16/03 t...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I think you want to add a column to show "Yes" if the same Claim ID have hybrid invoices(before 16/03 and after 16/03.) , and others show "No".

    You could build a calculated column.

    Caculated column = 
VAR _MIN = MINX(FILTER('Table','Table'[Claim ID]=EARLIER('Table'[Claim ID])),'Table'[Invoice Date])
VAR _MAX = MAXX(FILTER('Table','Table'[Claim ID]=EARLIER('Table'[Claim ID])),'Table'[Invoice Date])
Return
IF(_MIN<DATE(2016,03,16)&&_MAX>DATE(2016,03,16),"Yes","No")

    Result is as below.

    You can download the pbix file from this link: Conditionnal Column with a common Key

     

    Best Regards,

    Rico Zhou

     

    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
5 years ago

Thank you for your answer but actually there is 2 conditions regarding the date. 

 

Exemple :

Claim ID 1717 / Invoice Date : 17/02

Claim ID 1717 / Invoice Date : 27/07

 

There is 2 invoices with the same claims ID but at different Claim Date.

 

And my condition is the following if there is a same a claim ID for x invoices, one BEFORE the 16/03 and one AFTER the 16/03. 

Then, Yes otherwise No. 

 

I would like to be able to estimate how many invoices with the same Claim ID have hybrid invoices i.e. before 16/03 and after 16/03.

 

I hope I am clear, thank you very much for your help.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

I think you want to add a column to show "Yes" if the same Claim ID have hybrid invoices(before 16/03 and after 16/03.) , and others show "No".

You could build a calculated column.

Caculated column = 
VAR _MIN = MINX(FILTER('Table','Table'[Claim ID]=EARLIER('Table'[Claim ID])),'Table'[Invoice Date])
VAR _MAX = MAXX(FILTER('Table','Table'[Claim ID]=EARLIER('Table'[Claim ID])),'Table'[Invoice Date])
Return
IF(_MIN<DATE(2016,03,16)&&_MAX>DATE(2016,03,16),"Yes","No")

Result is as below.

You can download the pbix file from this link: Conditionnal Column with a common Key

 

Best Regards,

Rico Zhou

 

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