Evaluation of Lower Level Table ID Status against Higher Level Table ID Status

[Anonymous]

Hi All,

So, what I need to figure out is a way to do the following:

Assumptions:

• Table 1 has a set of IDs (Lvl 1) with their own status.
• Table 2 has a set of IDs (Lvl 2) with their own status, and a reference to Table 1 IDs (Lvl 1). 
  • There is a reference of 1 to many from Table 1 to Table 2, through the Table 1 IDs (Lvl 1).

Expected Outcome: Table 1 - Current Status column

• This Current Status column should evaluate from Table 2, the following:
  • If all or some Lvl 2 IDs are "In progress", then that Lvl 1 ID current status is "In Progress"
  • If all Lvl 2 IDs are "Not Started", then that Lvl 1 ID current status is "Not Started"
  • If all Lvl 2 IDs are "Done", then that Lvl 1 ID current status is "Done"
• I need this outcome to then be able to evaluate in Table 1, which are the status that do not match the "Original Status" with the "Current Status" via a True/False comparison, and then be able to update them accordingly.

Relationships:

Table 1 - High Level:

Table 2 - Low Level

Sample Data PBIX: https://drive.google.com/file/d/1LGcNerK8VxwDqf5pb7VzF5IabDo3pVaQ/view?usp=sharing

Thanks for your support!

Regards,

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution. 

Create a calculated column.

Column =
VAR _T =
    FILTER (
        'Table 2 - Low Level',
        'Table 2 - Low Level'[Lvl 1 ID Parent]
            = EARLIER ( 'Table 1 - High Level'[Lvl 1 ID] )
    )
VAR _InProgress =
    COUNTROWS ( FILTER ( _T, [Current Status] = "In Progress" ) )
VAR _NotStarted =
    COUNTROWS ( FILTER ( _T, [Current Status] = "Not Started" ) )
VAR _Done =
    COUNTROWS ( FILTER ( _T, [Current Status] = "Done" ) )
VAR _All =
    COUNTROWS ( _T )
RETURN
    IF (
        _InProgress > 0,
        "In Progress",
        IF ( _NotStarted = _All, "Not Started", IF ( _Done = _All, "Done" ) )
    )

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

I am trying this IF but it's not working, I am not fully understanding where I'm failing 😞 

IF (_InProgress > 0, 
        "In Progress", 
        IF(AND(  _Done > 0, _Done <> _All), 
            "In Progress",  
            IF ( _NotStarted = _All, "Not Started", IF ( _Done = _All, "Done" ) )))

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ，

Sorry for late back, you don't need to add other condition, because the if it don't meet all the below three conditions:

1. IF(_InProgress>0,

2. IF(_NotStarted=_All,

3. IF(_Done=_All,

It must be in the condition: If any DONE, but not all.

So just add a result in the if function will work, modify to:

Column =
VAR _T =
    FILTER (
        'Table 2 - Low Level',
        'Table 2 - Low Level'[Lvl 1 ID Parent]
            = EARLIER ( 'Table 1 - High Level'[Lvl 1 ID] )
    )
VAR _InProgress =
    COUNTROWS ( FILTER ( _T, [Current Status] = "In Progress" ) )
VAR _NotStarted =
    COUNTROWS ( FILTER ( _T, [Current Status] = "Not Started" ) )
VAR _Done =
    COUNTROWS ( FILTER ( _T, [Current Status] = "Done" ) )
VAR _All =
    COUNTROWS ( _T )
RETURN
    IF (
        _InProgress > 0,
        "In Progress",
        IF (
            _NotStarted = _All,
            "Not Started",
            IF ( _Done = _All, "Done", "In Progress" )
        )
    )

Result:

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

This has been REALLY helpful and it pretty much covers all scenarios! I do note that I missed one condition:

Condition Missing: If any DONE, but not all, then "In Progress"

If I understand correctly the column you have created, this would be something like:

IF ( _Done = > 1 & <> _All, "In Progress" )

Meaning, If the variable "Done Count" is more than 1, but different to the variable "all results", then it's In Progress.

But I just can't understand where to place it in the DAX 😞