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. 

Power BI Dataset Refresh Error for Column in Table contains a duplicate value

We have a Power BI dataset and report running for few month without refresh error. However it shows the following error and refresh stops.

Column 'PubsID' in Table 'FACTPubsRecord' contains a duplicate value '1115' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

After debugging the database tables, table relationships, columns, and measures, we were not able to resolve the issue. I started to rebuild the dataset and everything looks good. At the end when I document the dataset and set up the properties of the table, this is one "Key column" identified that is cuasing the problem. The column named PubsId that was unique before. However, it is no longer unique.

The Key Column in Power BI properties MUST be unique in order for Power BI refresh to work. The solution is to change it to PubsKey unique value to resolve the issue.

This is hard lesson that took me two days to finally resolve the isuse.