Get Activities by comparing between two or more projects in Power BI

[adarshthouti]

Hi, 

Here I am trying to compare two projects by creating a slicer visual where one is "BASELINE" and the other is "CURRENT", so when I select a project_id from "BASELINE" and another project_id from "CURRENT" I want to get the activities which are present between these projects. How can I achieve it? 

I have created two calculated tables for "BASELINE" and "CURRENT" as 
1) BASELINE = ALL(TASK) 
2) CURRENT = ALL(TASK)

So in the first slicer visual, I used project_id from "BASELINE" and in the second slicer I used project_id from "CURRENT". I have also created a validation measure for baseline and current, suppose we select a project_id in the BASELINE slicer, that selected project_id will not show in the CURRENT slicer. 

What will be the relationships between "TASK", "BASELINE" and "CURRENT"?
Here I am also trying to compare whether the activities exist in the baseline or current project, so I have attached the images for reference. I am not getting the desired output while comparing.

Please help me out with this issue as soon as possible. MeasureMeasureI am getting the output like this

[Anonymous]

Hi @adarshthouti ,
According to your description and the formulas provided, regarding the relationship between "TASK", "BASELINE" and "CURRENT": since both "BASELINE" and "CURRENT" are derived from the "TASK" table, they essentially share the same structure and data. "BASELINE" and "CURRENT" are both derived from the "TASK" table, so they essentially share the same structure and data, albeit in different ways depending on the filter chosen by the slicer. They essentially share the same structure and data, although depending on the slicer, different filters are selected. The relationship from TASK to BASELINE and CURRENT is essentially a one-to-many, since TASK is the source table. TASK" is the source table.
Depending on your desired outcome, what you want to achieve is that if a condition is filtered in BASELINE, the condition will not appear in CURRENT.
You can create a meaure like this

Exist_in_Current = 
IF(
    CALCULATE(
        COUNTROWS('CURRENT'),
        FILTER(
            ALL('CURRENT'),
            SELECTEDVALUE(BASELINE[proj_id]) <> SELECTEDVALUE(TASK[proj_id]) && SELECTEDVALUE('CURRENT'[proj_id]) <> SELECTEDVALUE(TASK[proj_id])
        )
    ) > 0 ,
    "Yes",
    "No"
)

Final output

Best regards,

Albert He

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

[adarshthouti]

Thank you Albert, It is working fine, but when I am trying to compare multiple projects I am getting all the projects that are present in the table but I only want to get the selected project activities. 

[Anonymous]

Hi @adarshthouti ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Meaure Exist_in_Baseline and Exist_in_Current are the same as in the previous example, with the addition of a meaure for determining whether or not the filtering conditions are met

IsBaseline or IsCurrent = 
IF(
    SELECTEDVALUE(Task[proj_id]) = SELECTEDVALUE(BASELINE[proj_id]) || SELECTEDVALUE(Task[proj_id]) = SELECTEDVALUE('CURRENT'[proj_id]),
    1,
    0
)

Apply the measure to the filter on this visual and set it "is 1"

Final output

Best regards,

Albert He

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

[adarshthouti]

One more thing is like for example from the below image "C321" and "R214" are present in both the projects so the Exist_in_baseline and Exist_in_current should be "Yes" in both the cases.

[adarshthouti]

Hello Albert, 

Thank you for the solution there was a mistake on my end, The output that I want to get is different where I have replaced the last image with new image. So can you please help me with that one change by refereing once again the last image in the question?

Please check the bottom two images as well to modify the measure, and the resultant output should be like bottom two images.

[Anonymous]

Hi @adarshthouti ,
Based on your changes, I modified my dax as follows

Exist_in_Baseline = 
IF(
    CALCULATE(
        COUNTROWS(BASELINE),
        FILTER(
            ALL(BASELINE),
            SELECTEDVALUE('CURRENT'[proj_id]) <> SELECTEDVALUE(TASK[proj_id]) 
        )
    ) > 0 ,
    "Yes",
    "No"
)

Exist_in_Current = 
IF(
    CALCULATE(
        COUNTROWS('CURRENT'),
        FILTER(
            ALL('CURRENT'),
            SELECTEDVALUE(BASELINE[proj_id]) <> SELECTEDVALUE(TASK[proj_id]) 
        )
    ) > 0 ,
    "Yes",
    "No"
)

Fianl output

Best regards,

Albert He

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

[adarshthouti]

One more thing is like for example from the below image "C321" and "R214" are present in both the projects so the Exist_in_baseline and Exist_in_current should be "Yes" in both the cases.