Measure that count the Project on the based of Status Field

[H_s]

Hi,

I want to create a measure to calculate the count of the project on the based of Status field. The status field have three status value as "Yet to Start", "In progress" and "Closed".
The condition is: 
Let assume that  there are 3 projects A,B and C and these are in there audit stage and these can have one aur multiple defect with unique defect Id. So if the value of Status field for any project is "Yet to start" then this project should count in Yet to start stage and if the value of Status field for any project is "In Progress" then this project should count in In Progress and same if the value of Status field for any project is "Closed" then this project should count in Closed stage.

And if the value of Status field for any project is "Yet to start" and  "In Progress" as well then this project should count in Yet to start stage. 

And if the value of Status field for any project is "Yet to start" , "In Progress"  and "Closed "as well then this project should also count in Yet to start stage. 

And if the value of Status field for any project is  "In Progress"  and "Closed "as well then this project should  count in In progress stage.

Mean if for any project the value of status field is more than 2 or more than 2 then it should be count in its initial stage among the status value.

Please share your suggestion and guide me.
thanks.

[v-nuoc-msft · Accepted solution]

Hi @H_s 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create measures.

First, mark each of the three status you mentioned with numbers.

Mark_NUM = 
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Table'[Status]) = "Yet to Start", 1,
        SELECTEDVALUE('Table'[Status]) = "In progress", 2,
        SELECTEDVALUE('Table'[Status]) = "Closed", 3
    )

Query the minimum mark_num under each project.

Min_Mark_NUM = 
    MINX(
        FILTER(
            ALL('Table'), 
            'Table'[Project] = MAX('Table'[Project])
        ), 
        'Table'[Mark_NUM]
    )

Query initial status.

Initial Status = 
    IF(
        [Mark_NUM] = [Min_Mark_NUM], 
        VALUES('Table'[Status]), 
        BLANK()
    )

Here is the result.

Regards,

Nono Chen

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

[H_s · Accepted solution]

@v-nuoc-msft thanks!! it's working....thank u so much.

[v-nuoc-msft · Accepted solution]

Hi @H_s 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create measures.

First, mark each of the three status you mentioned with numbers.

Mark_NUM = 
    SWITCH(
        TRUE(),
        SELECTEDVALUE('Table'[Status]) = "Yet to Start", 1,
        SELECTEDVALUE('Table'[Status]) = "In progress", 2,
        SELECTEDVALUE('Table'[Status]) = "Closed", 3
    )

Query the minimum mark_num under each project.

Min_Mark_NUM = 
    MINX(
        FILTER(
            ALL('Table'), 
            'Table'[Project] = MAX('Table'[Project])
        ), 
        'Table'[Mark_NUM]
    )

Query initial status.

Initial Status = 
    IF(
        [Mark_NUM] = [Min_Mark_NUM], 
        VALUES('Table'[Status]), 
        BLANK()
    )

Here is the result.

Regards,

Nono Chen

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