Using Or Conditions between Slicers to filter Primavera Schedule using DAX

The Interactive report is published here,  you can download the PBIX here.

In PowerBI or Tableau or any BI solution by defaults when you have multiple slicers, the filter conditions is always, AND, in this particular case, the user want to filter a schedule based on a start date  OR the end date OR the activity status, basically how to reproduce the following Primavera Filter using DAX

Primavera

 

 

Using the normal slicers will not work as it will simply show the activities where all the conditions met, instead we will use some DAX techniques (disconnected slicers and filter using the result of a measure) to get a new behavior.

 

Let’s build a simple data mode.

1-The main table is task: it contains Activity ID, Activity Status, Start, Finish

2- add a date Table, Start_Date, don’t link it to any table, use it in a slicer , the table is generated using Powerquery,

3- add another Date Table, Mstdate, don’t link it either, use it in a slicer

model

 

4- add Activity status Table, don’t link it, , use it in a slicer

5- add this measure

Filter_measure = CALCULATE(COUNTROWS(task),

FILTER(task, ([Start] <= LASTDATE(Start_Date[Date])

&& [Start]>= FIRSTDATE(Start_Date[Date]))

||

([finish] <= LASTDATE(Finish_Date[Date])

&& [finish]>= FIRSTDATE(Finish_Date[Date]))

||

IF(NOT(ISFILTERED(‘Activity status'[Activity status])),BLANK(),’task'[Activity status]IN ALLSELECTED(‘Activity Status'[Activity Status]))))

 

Add this measure to the matrix visual as a filter only, and make it not equal null, and voila

result

Advertisements

Load polygons geometry into PowerBI data model

Starting a new project that involve plotting a relatively big map ( 3000 polygons), it should be straightforward, as I have done it before, but a limitation in my prefered PowerBI custom visuals had an interesting result.

1-  synoptic panel: in my view it is the best visual to show custom map, ( floor plan, general layout etc), unfortunately not this time, my new map has 3000 polygons, when I tried to plot some attributes, I get the equivalent of Windows screen of death for PowerBI, too many values !!!

1

That’s bad, I filled a bug report, the author was kind enough to reply that it will be fixed in a next release, (something to do with incremental loading) fine let’s try other options

2- Shape map : (it did not even render the polygons correctly, and to be honest the visual did not receive any update since the first time I use it, 2 years ago)

3- Mapbox: showstopper, to load your own shape file and keep it private, you need a business plan.

I am stuck, I can’t plot a 3000 polygons map in PowerBI, let’s try Rstats.

4-Leaflet: is very versatile mapping engine, right now I use it in other projects (outside of PowerBI) and it plot 60K points and nearly 500 polygons in sub 2 seconds, but there is a problem, Leaflet output is HTML which is not supported under PowerBI, I never manage to make decent screenshot, something to do with the zoom.

5-ggplot2 + SF : quick google and got this code

library(sf)

library(tidyverse)

map_shp <- read_sf(‘C:/Users/mimoune.djouallah/test.geojson’) map =dataset %>%

left_join(map_shp, ., by = c(‘id’ = ‘id’)) %>%

ggplot() +

geom_sf(aes(fill = type),lwd = 0)

map

2

Great, I have my map now ( this is only a portion, I can show the whole layout as it is proprietary), there is only a little problem, code take 25 seconds to render, it is very annoying, especially as in PowerBI you touch anything, and all the visuals render again.

my first thought maybe the join between PowerBI dataset ( dataframe) and the geojson is slow !!! the beauty of R integration in PowerBI is: you click on R icon, a new window open with an empty dataset, then you add the columns you want to analyse, yes that simple no ETL, no messing with data, you have to the mighty data model.

Loading Geometry data into PowerBI data model ?

 

After reading the SF documentation, I learned all I need is a dataframe  with a column that store geometry data, too easy just use QGIS , save as csv and make sure to select geometry, WKT

Capture

Now I loaded the csv file to the data model and join it with the attribute

3

Now here is the new code, notice, we don’t load any external file, all data is from the data model

library(sf)

library(tidyverse)

map_file= st_as_sf(dataset, wkt = “geometry”)

map = ggplot(map_file) +

geom_sf(aes(fill = type),lwd = 0)

map

basically, you need just to tell SF which column represent the geometry , unfortunately it is still slow maybe now it is 24 seconds

Another round of googling and I find the issue ggplot2 is very slow to render, no problem, this is R after all, there are a least a couple dozen of packages that do the same thing 🙂

6-Pure SF

 Turn out SF is not only to manage Geometric dataframe but it can plot too,( who would thought that)

library(sf)

map = plot(st_as_sf(dataset, wkt =geometry”)[“type”])

map

 render in less than 2 seconds, happy dance  

 

Bonus point, Cross-filing geometry

 My purpose to load geometry was just to speed ggplot2, but hold on, the geometry is in the data model, not only the attribute change ( install quantities, category etc), I can even filter polygons on the fly !!!, this is quite amazing, I can load all the layers and filter out any shape as I want.

4

HTML map using R, Part 1

I always tried to learn R, but I never succeed, I did not like it, after enjoying the GUI of PowerQuery, using R was like a self-inflicted torture. But obviously I was wrong as usual, R is not only for data cleaning.

in a PowerBI usergroup , I heard someone talking about R, and I made a mistake of telling him what R can do that PowerBI can not do ?, all I can say is I wish if I did not ask him this question, anyway, in defending R, he said something about generating HTML file and boom, that’s exactly what I was looking for, a file that anyone can read and it has interactivity and it works offline.

my problem is, I build very nice interactive map visualization, but for large distribution, I just print it to pdf, which obviously fail the purpose, not everyone has access to PowerBI service, and for external parties ( client, subcontractor), you can’t reasonably expect them to subscribe to PowerBI service just to see a map :), using Excel 2016 was an option but unfortunately some functionalities are still missing (can’t show a label) and honestly the integration of 3D map with Excel looks like it was a second thought by the developers.

The good news is, R has an excellent integration of PowerBI, so instead of recreating the wheel, just use R for the stuff that can not be done natively in PowerBI.

I hope to publish multiple blogs to document my workflow.

In my real case scenario which I can not publish for obvious reason, we need to view the status (Completed, or not) of nearly 50,000 Piles, show all the piles will clutter the view, instead, I found by accident that a library called Leaflet (the Engine used by MapBox), has the ability of showing layer only when you zoom.

I will use a dummy data, (literally a foundation on a mountain in a New Zealand as an example),

Capture

the data source is an excel file with the pile location and topojson file to show the foundations location all generated using the Excellent QGIS.
the R code is

library(readxl)
library(leaflet)
library(htmlwidgets)
#read Excel Data
data <- read_excel("pile.xlsx", col_types = c("text","text", "numeric", "numeric"))
#read json
map_data <- geojsonio::geojson_read("foundation.json",what = "sp")
######################################
map <-leaflet(map_data) %>%
addTiles(urlTemplate = "https://mts1.google.com/vt/lyrs=s&hl=en&src=app&x={x}&y={y}&z={z}&s=G", attribution = 'Google') %>%
addPolygons()%>%
addCircleMarkers(lng = data$x, lat = data$y,radius = 3)
map
saveWidget(map,file="foundation.html",selfcontained=TRUE,title = "foundation")

the result is a self contained html, at the moment, it is show only 2 foundations and some piles, in the next blog post, we will add zoom, and how to filter layers, and show labels and stuff.

the result is here, and the source data is located in this github repository.

 

ScheduleReader

 

 

Introduction to ScheduleReader™

Companies and organizations always strive to work hard towards stimulating employees’ motivation, improving company’s performance and, ultimately, achieving project success.

At times when all these aspirations depend on different factors, it is inevitable to adopt certain practices that would advance the working environment. For example, for the companies working on complex projects in large scale sectors such as construction, industrial manufacturing, oil and gas, and public utilities, it is imperative to practice project management.

The process of project management consists of several phases that are critical for successfully finishing a project so each needs to be carefully and properly executed. But the project management flow continually needs to be ameliorated so that key project’ deliverables are achieved. One essential factor in the project management process is the use of good project management software.

Organizations in the above-mentioned sectors generally plan their projects in Oracle® Primavera P6. For accessing these project plans, which are stored in Oracle’s database, is required to have a license. Obtaining licenses for all project participants might be costly, therefore many decide to buy a couple and to go with the .pdf reader as the tool for reading project plans.

But the upper management isn’t fully aware that this can be a risky way of managing a project, which can cause the project to fail. It can complicate the performance of all project participants and consequently prevent the progress or accomplishment of the project.

ScheduleReader is the software solution which can replace the .pdf reader and simplify the process of managing a project. It is considered as effective solution that contributes to all four phases that comprise the process of managing a project. By implementing the software in a specific working environment every project participant will be able to access the project plans without the need for an expensive Oracle license. It is possible to make the management of the project faster, more comfortable and definitely more functional.

The implementation of ScheduleReader in your environment can seem a challenging and unpleasant change for your project team, however it is extremely simple as ScheduleReader is a completely standalone solution and at the end, it will certainly bring a positive impact to your organization.

primaverareader

It is developed as an independent standalone software application, with the objective to offer a simple way of viewing project data without having Primavera P6 license. It supports project schedules exported in .XER, .XML and .XLS file formats. The viewer is designed with a highly customizable interface and overall organizational structure that resemble Oracle® Primavera P6.

This enables users to easily navigate through different tasks in the project. Choosing ScheduleReader™ as a software solution offers advantages to all project participants by giving them the possibility for a dynamic overview of the project, simple comparison of project’s baselines, easy navigation through activities and prompt reporting on project’s status.

Key benefits of ScheduleReader™ that boost success

As must have P6 addition to you project management software suit ScheduleReader™ offers   improved visualisation of project data and sharing of project information.

When it comes to the company’s and project teams’ benefits, it guarantees establishing better communication and collaboration. On the other hand, when it comes to the project, ScheduleReader™ enables better budget and time management, facilitates the process of monitoring and guarantees improved control, contributing to on schedule project delivery.

ScheduleReader Standard and PRO

There are two versions of the ScheduleReader software that are developed to answer different company and project needs. ScheduleReader Standard is designed to modernize the work and provide benefits for all project teams and stakeholders who are currently viewing the plans in PDF or XLS.

On the hard, for the experienced project professionals who need to analyze schedules, create reports, better visualize and present summaries of complex project data, ScheduleReader PRO was developed – a version consisted of ScheduleReader Standard with additional features for reports generation and KPI analysis that allows you to quickly generate rich visual reports with a single click of a button.

What can you do with this software?

There are many key features that help positioning ScheduleReader ™ as the optimal solution for viewing project plans created in Oracle® Primavera P6. Some of them are the following:

  • Activity View

Allows users to have a clear overview and a detailed graphical representation of all open project activities. With this customizable window, users can have an organized view of project’s specifics; they can create Groups, Filters and Sorts with multiple codes and UDFs; save the views as a layout and even customize the bars in the Gantt chart.

  • Trace Logic View

Combined with ‘Activity View’, this feature can be used for in-depth analysis of the project schedule activities. For example, when one activity is selected in the ‘Activity View’ the user can view its predecessor and successor activities in the ‘Trace Logic View’. For the users who would need to compare up to four baselines for a particular project schedule and get a graphical display of the project’s specifics, ScheduleReader™ provides the feature ‘Baselines View’.

Activities

  • Progress Update

Very important feature which gives the possibility to give constructive feedback, as well as to propose updates for % Complete, Actual Start, Actual Finish, Activity Status and Activity Codes, which can be accepted and rejected by their superiors. This feature is perfect for field work as it gives you the opportunity to propose activity updates without interfering with the original project schedule file.

Progress-update

  • Resource Usage Profile View

By setting the timescale for displaying data values, the users can view the quantity information for resources or roles or analyze their costs.

  • S-Curves

Help for a graphical presentation of cumulative costs and units. With this feature users can see how planned and actual quantities are plotted.

Resource-usage-profiles

  • Bar Customization

Enables adding new bar types, removing the unnecessary ones or making modifications. User can create view that is specific for its working team and share it with his team members.

  • Graphical Reports Generation

Create default and custom reports, analyze project KPIs and schedule quality, measure the project progress and communicate information more effectively with all project stakeholders.

Reports-and-dashboards

Note: this is a guest Post by ScheduleReader

Float Erosion report between two XER

I just added a new report in the Xer reader to show the float Erosion Report between two XER, I am using the “Harbour Pointe Assisted Living Center” from Primavera P6 default installation as example.

1- Download the pbix file here

2- Save a two XER in this folder C:\XER

2

3- Refresh PowerBI Desktop

3

4- Review the report, and export for Excel for sharing/Further analysis.

4

5- if you have PowerBI service in your company then publish it there, so other users can interact with the reports.

let me know if you want to see other reports.

 

 

Rewriting Xer Reader using PowerBI

4 years ago, we had to customize the xer parser for a client, that work end up as Xer Reader, turn out the file was very popular, but unfortunately, as it was written in VBA, users get all kind of different errors, and I was not able to provide a proper support.

now I am trying to rewrite the same  Excel Macro but now using PowerBI, the file is hosted in github

in order to open a pbix file, you need PowerBI Desktop, it is a free download

I think it will be an interesting experience, some stuff are much easier using VBA, and there is no equivalent in PowerBI, I will try to document the progress.

in the main time here is my progress so far

stay tuned

Using QGIS to prepare Custom Maps in PowerBI

This blog post is to document my first experience with mapping in PowerBI, usually the reports we produce are time series and pivot tables, but in this case, there is a geographic dimension to the data, so I thought it is a good opportunity to try map chart in PowerBI, it turn out, it is a bit difficult than I thought.

So the data is in the thousand of piles, a lot of piles in a huge area, my first attempt was just to load the data in PowerBI and view it in a map, for some reason, PowerBI show an empty map.

PowerBI expect the data to be in latitude and longitude format, my the data is using easting and northing, I had to convert it, there are plenty of online converter, but there are not practical, as they don’t support batch processing, converting point by point is not an option.

After some googling, I find this excellent open source software QGIS, it was very straightforward, the software automatically convert the coordinates reference system (I think the technical term is reprojection), my data is GDA94 / MGA zone 55 and the result should be in WGS 84.

Voila the data is ready for PowerBI,

map-1

That’s a bit disappointing, PowerBI complain it cannot show all the points ( PowerBI has a maximum of 3500 points per chart).As a comparison, this is how Tableau show the data

tableau

Tableau doesn’t have a limitation on the number of points.

 

Alternative approach

 

As PowerBI cannot show all the point, one solution is to create a shape file that group the points into smaller areas, and again, it was trivial to be done in QGIS.

QGIS will group the point based on a filed you provide.

Group

 

sub-array

And volia

qgis

 

QGIS save the layer in ESRI shapefile, PowerBI require TopoJSON, I used the excellent tool mapshaper.org

Just make sure you import all the files not only .shp

export

Now the TopoJSON is ready to be loaded in PowerBI

topojson

 

Time to celebrate 🙂  not really there is a problem, I want to show different colour based on the status, if a sub-array is completed, I want it to show Yellow, if it is > 75 %, I want another colour and so on, the shape Map in PowerBI does not offer this option.

Fortunately Synoptic Panel   has more options, it use SVG as a map format, which I got from mapshaper.org

filled map

 

Preparing the custom map is a one time operation,  the color will change as per the construction progress, you need just to assign which measures you want to show

measures

What’s Next

As long as your data model is properly built,  you can do a lot of interesting stuff, you can filter by type of works (piling, Tracker installation, PV Modules), you can click on one sub-array and see in details what’s have been installed and what’s missing.