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.

How to Sort by Month Year Name in Power BI report

When you create Power BI report based on Month and Year calendar, the default sorting i s based on the alphabetic name like then screenshot below.

If we need to create a report that will display the month and year combination and sorted by the time, you will need to create a different column and apply the sorting. Here are the steps.


First you will need to crate a new column like below:
MonthYearOrder = CONCATENATE(MyCalendar[Year],right(CONCATENATE("00",MyCalendar[MonthNumer]),2))

Then click the column you need to display like "MMM_YYYY" and select "Sort by column". Pick the new column "MonthYearOrder" just created.

In the report, you will need to pick either acceding or descending to display the sequence correctly.

You could use this method to apply the sorting on other columns like "Department" based on the different order you prefer to display.

Power BI Streaming Dashboard example

There are many different ways to present real data report and dashboard in Power BI. One of the way is to use PubNub that could make your data streams available on the Microsoft Power BI platform.  Here is the detailed steps to use PubNub for Power BI.

The first step is to create a Power BI dataset from Power BI service.

The second step is to create the dataset by connecting to PnbNub. The data example is on the PnbNub site. You need both Channel and Subscription Key to connect.

The third step is to create the dashboard from Power BI service. You should select the "Streaming dataset".

The forth step is to add tiles to the report as in the below screenshots.

There are some gotchas you should be aware of the Power BI streaming. Please read Microsoft blog for details.

There are few different ways for Power BI streaming, you could also use Flow automation to push data to Power BI dataset.

Create summary table and ensure no empty value for Power BI

If you need to create a summary table for Power BI report, you should be careful not to return empty value. Here is one example.

NoActionTakenApprovalTasks = SUMMARIZE(DimApprovalTasks,
   DimApprovalTasks[Approver],
   DimApprovalTasks[ApprovalRole],
   "Total Tasks", countrows(DimApprovalTasks),
   "Total No Action Taken Tasks",  IF(
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")= BLANK(),
0,
CALCULATE(COUNTROWS(DimApprovalTasks), DimApprovalTasks[Status]= "No Action Taken")
)
)


In this example, we add an IF condition to return either 0 or value if not empty. This will be same to create measure to ensure value set to 0 when empty value returned. An example is below.

Total Approved Tasks =

IF(

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

) = BLANK(),

0,

CALCULATE (

COUNTROWS ( DimApprovalTasks ),

DimApprovalTasks[Status]= "Approved"

)

)

Power bi calculated column multiple if statements example

We have a requirement to display the percentage distribution in Power BI. The percentage can be any value from 0% to 100%. However, the report should show the percentage range like 10-20, 20-30, etc.

In order to do this, a calculated column with multiple if statements will be required. If you know the syntax, it will be quite simple. Here is the example.


Percentage Range =
IF([No Action Taken %]=0.00,"0",
 IF(AND([No Action Taken %]>0.00, [No Action Taken %]<=0.10),"1-10",
  IF(AND([No Action Taken %]>0.10, [No Action Taken %]<=0.20),"11-20",
    IF(AND([No Action Taken %]>0.20, [No Action Taken %]<=0.30),"21-30",
      IF(AND([No Action Taken %]>0.30, [No Action Taken %]<=0.40),"31-40",
        IF(AND([No Action Taken %]>0.40, [No Action Taken %]<=0.50),"41-50",
          IF(AND([No Action Taken %]>0.50, [No Action Taken %]<=0.60),"51-60",
            IF(AND([No Action Taken %]>0.60, [No Action Taken %]<=0.70),"61-70",
     IF(AND([No Action Taken %]>0.70, [No Action Taken %]<=0.80),"71-80",
   IF(AND([No Action Taken %]>0.80, [No Action Taken %]<=0.90),"81-90",
IF(AND([No Action Taken %]>0.90, [No Action Taken %]<1),"91-99", "100"
 
)))))))))))

There is another option to use switch you could also try.

Populate user office location for Power BI

There is a need to populate user office location for Power BI. Here is one of the quickest way to get this information trough AD Powershell.

I've quickly developed a script to have user office populate in a csv file and upload to SharePoint for Power BI to consume. Here is the script based on AD Powershell.


$ADUsers =  Get-ADUser -Filter *  -Properties physicalDeliveryOfficeName  | where physicalDeliveryOfficeName  -ne $null
$outputFile = "C:\Harry\Projects\SPCOE\Scripts\GetADUsers\Output\ADUsers.csv"

$rows = @()

foreach($ADUser in $ADUsers)
{
    $rows += New-Object -TypeName PSObject -Property @{                                                                                                                                   

                                            Name = $ADUser.Name
                                            UPN = $ADUser.UserPrincipalName 
                                            Office = $ADUser.physicalDeliveryOfficeName                                       

                                            } | Select-Object Name,UPN,Office

}

$rows | Export-Csv $outputFile  -NoTypeInformation -Force  -ErrorAction SilentlyContinue