Urgent calculate value same column

[Anonymous]

Hi all!

I need to calculate the value of the state "in progress" for each ID by checking if the state column has "in progress" and "prioritized", otherwise I will not return anything. In this case below should return to ID 11 = 0 (contains only "in progress") and ID 8737 = 5 (contains "in progress" and "prioritized").

Could you please help me using DAX?

Work Item Id	Value	State
11	1025	In Progress
11	69	Done
11		New
8737		New
8737	2	Approved
8737	1	Prioritized
8737	5	In Progress
8737	14	Done

[amitchandak]

Use concatenatex and search

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

https://docs.microsoft.com/en-us/dax/search-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

I tried the DAX below but unfortunately it only worked for id 8737 which has both states (prioritized and in progress), but in id 11 it should show 0 since it only has in progress. What should I do? Thank you again!

FEATURE WAITING =

VAR _state = CALCULATE(CONCATENATEX('MEDIAS STATUS';'MEDIAS STATUS'[State];" ");ALLEXCEPT('MEDIAS STATUS';'MEDIAS STATUS'[Work Item Id]))

VAR _value= CALCULATE(AVERAGE('MEDIAS STATUS'[STATE_INTERVAL]);'MEDIAS STATUS'[State]="In Progress")

RETURN

IF((SEARCH("In Progress";_state;1;0)+SEARCH("Prioritized";_state;1;0))>=2;_value;0)

Work Item Id	State	Value
11	Done	1025
11	In Progress	1025
11	New	1025
8737	Approved	5
8737	Done	5
8737	In Progress	5
8737	New	5
8737	Prioritized	5

[amitchandak · Accepted solution]

I used the same formula you created and getting 0 for 11

link for pbix : https://www.dropbox.com/s/jcvon4c9pg7za52/WorkItem.pbix?dl=0

[Anonymous]

I tried again and now it works. Thank you!