Forum Discussion

adarshthouti's avatar
adarshthouti
Frequent Visitor
2 years ago

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

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

Need Help
Tips and Tricks
Tutorial Requests

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      adarshthouti
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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's avatar
      adarshthouti
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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