DAX, worst case roll-up calc

[athomp15]

Hi Everyone,

Just wondering if someone could give me a pointer on how to calculate a "worst case" per location on a project status table.   My data looks like this

Country, Project, Project Completed, Project In Progress, Project Not Yet Started

=====================================================

CountryA, Project A, "Yes", "No", "No"

CountryA, Project B, "Yes", "No", "No"

CountryA, Project C, "No", "Yes", "No"

CountryA, Project D, "No", "No", "Yes"

CountryB, Project A, "No", "No", "Yes"

CountryB, Project B, "Yes", "No", "No"

CountryB, Project C, "No", "Yes", "No"

CountryB, Project D, "No", "No", "Yes"

CountryA, Project A, "Yes", "No", "No"

CountryA, Project B, "Yes", "No", "No"

CountryA, Project C, "Yes", "No", "No"

CountryA, Project D, "Yes", "No", "No"

I would like to give each country a status.  If a country has any projects "Not started" then they get a status of "Not started", if they have any projects "in progress" then their status is "In progress", and finally if a country has only completed projects they get a status of "Completed" projects

Then I would like to count the countries by status in 3 different measures.

From the data above

#Number of Countries With Projects Not Started = 2

#Number of Countries With Projects In Progress = 0

#Number of Countries With All Projects Completed = 1

Thanks in advance

Alex

[Eric_Zhang · Accepted solution]

@athomp15

You can also simply replace the first 3 steps in my previous reply with a calculated column.

Attribute = SWITCH(TRUE(), 'Table'[Project Not Yet Started]="Yes","Project Not Yet Started",'Table'[Project In Progress]="yes","Project In Progress","Project Completed")

[athomp15]

Thanks Eric that really helped me out - many thanks

Alex

[athomp15]

Hi Eric,

thanks so much for getting back.  I will give it a try and confirm.

Thanks

Alex

[Eric_Zhang]

---

@athomp15 wrote:

Hi Everyone,

Just wondering if someone could give me a pointer on how to calculate a "worst case" per location on a project status table.   My data looks like this

Country, Project, Project Completed, Project In Progress, Project Not Yet Started

=====================================================

CountryA, Project A, "Yes", "No", "No"

CountryA, Project B, "Yes", "No", "No"

CountryA, Project C, "No", "Yes", "No"

CountryA, Project D, "No", "No", "Yes"

CountryB, Project A, "No", "No", "Yes"

CountryB, Project B, "Yes", "No", "No"

CountryB, Project C, "No", "Yes", "No"

CountryB, Project D, "No", "No", "Yes"

CountryA, Project A, "Yes", "No", "No"

CountryA, Project B, "Yes", "No", "No"

CountryA, Project C, "Yes", "No", "No"

CountryA, Project D, "Yes", "No", "No"

I would like to give each country a status.  If a country has any projects "Not started" then they get a status of "Not started", if they have any projects "in progress" then their status is "In progress", and finally if a country has only completed projects they get a status of "Completed" projects

Then I would like to count the countries by status in 3 different measures.

From the data above

#Number of Countries With Projects Not Started = 2

#Number of Countries With Projects In Progress = 0

#Number of Countries With All Projects Completed = 1

Thanks in advance

Alex

---

@athomp15

Here's my approach for your reference.

1. Replace the "No" to blank values in those 3 columns

2. Unpivot those 3 columns

3.Filter out the blank vlaues

4. create a calculated column as 

Country_status = 
IF (
    CONTAINS (
        CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Country] ) ),
        'Table'[Attribute], "Project Not Yet Started"
    ),
    "Project Not Yet Started",
    IF (
        CONTAINS (
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Country] ) ),
            'Table'[Attribute], "Project In Progress"
        ),
        "Project In Progress",
        "Project Completed"
    )
)

5. Create a calculated table as below.

Table 2 = VALUES('Table'[Attribute])

6. At last, create a measure as below.

cnt = IF(ISBLANK(DISTINCTCOUNT('Table'[Country])),0,DISTINCTCOUNT('Table'[Country]))

See more details in the attached pbix file.