Power BI new column based on other table

 We have a Power BI report need to get the total "Approver Tasks" for one submitted "PubsRecord". There are few options and the best way is to calculate on the database view if possible. If you do not have option to modify database, you could use Power BI calculation. 

If you need to have multiple filter and conditions, certain format need to be implemented. Here are two proposals and one will have error.

This one is correct one.

Number Approval Tasks =

IF(

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

 DimApprovalTasks[LatestTask]= "Yes",

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] )

    )= BLANK(),

    0,

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        DimApprovalTasks[LatestTask]= "Yes",

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] )

    )

)

 

This one will have error.

Number Approval Tasks =

IF(

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]= "Yes"),

    )= BLANK(),

    0,

    CALCULATE (

        COUNTROWS ( DimApprovalTasks ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[PubsKey] = FACTPubsRecord[PubsKey] ),

        FILTER ( DimApprovalTasks, DimApprovalTasks[LatestTask]= "Yes"),

    )

)

You will notice the combination of "Filter" and other conditions will give you the flexible way to get calculation against any other tables.