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_...
Need Help
Tips and Tricks
Tutorial Requests
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

      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.