jump to navigation

Power BI – Date Filter Selection 1 July 2020

Posted by David Wilson in General.
add a comment

#PowerBI
#DAX
#DateTime

#UTCNOW

Last month I presented via Zoom at a Wellington Power BI User Group meeting. The subject Date and Time – it’s a recurring theme in Q&A in a number of forums at the moment. If you’ve got questions on that I highly recommend you download an inspect the PBIX file that accompanies this post. The trick then is to publish it to the Power BI service and note the differences (or not) between the dates and times on the Service vs. your desktop.

In that same PBIX file was a TAB for Date Filter Selection that demonstrated a special ability to always filter dates based on a calculated column. The best example of this is where you want to “default” to “Now”… naturally “Now” is a constantly moving target and Power BI doesn’t let you establish moving default values…. but it does and this page demonstrates that work around.

In addition I have also added a filter that allows the user to select the current week (or week ending). This too is demonstrated on that same page.

1. The “User Date” column is a DAX created column. It will contain text of a Date string or “Now!”. Selecting Now! in the filter will select the date row as shown (2 July 2020). If the filter never changes and the user reloads the page on another day, Now will effectively shift days.

2. The “User Date WeekEnding” column a DAX created column. It contains a text string, that looks kinda like a date, that aligns the Date to a weekending (or beginning – you choose). Selecting any given WeekEnding in the filter (shown here in BLUE) filters the date table to be all the days of that week.

Here’s an image of the Date Filter Selection tab.
Below please find the link to the PBIX file for you to inspect and understand.

https://sites.google.com/site/dgwilson65/DateandTime.pbix?attredirects=0&d=1

I’m keen to get feedback on this – has it been useful for you? Please use the comments area.

– David

TrafficCamNZ – Desktop – time for an update 6 June 2020

Posted by David Wilson in General.
add a comment

#TrafficCamNZ
#macOS

TrafficCamNZ Desktop for macOS has received some enhancements recently that I think help improve the look and usability of the program.

So far the changes/enhancements look like this:
● Bug fixes for for background thread initiation when building on a later version of the OS (macOS Catalina) – there’s a good chance a crash may have been exterienced if autorefresh was enabled – that’s now been fixed
● Improvements to messaging when images are unable to be retrieved – you can now read the errors!
● Increased the size of the text displayed under images to improve readability
● Built on the latest macOS – Catalina.
● Fixed some Storyboard constraint errors/warnings – always good housekeeping

You can tell from the screenshot below that it’s now dark and wet outside! You can also see the increased font size of the image description below the images… and that the description now also appears even below the images that have failed to be retrieved. Error messaging is also larger on each image.

– David

David Wilson
https://dgwilson.wordpress.com
https://nz.linkedin.com/in/dgwilson65

PowerBI DAX, Slicers, Disconnected Tables and using Relationships 10 February 2020

Posted by David Wilson in General.
add a comment

#PowerBI

In a previous post I had put forward a solution for user scenario where a SELECT tab presents a list of Companies that the user can choose one company from and on another tab the user is presented with that same company and others in the same Industry.

The solution(s) are not easy because the slicer from the SELECT table is Sync’d to the tab where I want the results to be displayed and by default restricts the display to the single selected company.

The original post solutioned that it could be solved with a “disconnected copy of the company table” and some DAX. Through one of the comments “AkhilAshok” proposed a upgraded solution with a nice clean implementation. It is worth looking into this as there are a couple of tricks to understand and trying to retrofit it into an existing model may cause some grief if you do not understand the implementation in the demo.

2020-02-10 07_17_26-Industry Selection Fix - Power BI Desktop

So if you are looking to implement this I recommend you consider the implementation carefully in terms of how it fits into your model with regard to the relationships of your tables.

The picture above shows (with the red square) my original “disconnected copy of the company table”. The table with the green highlight around it is also a copy of the company table with an active and unactive relationship back to the Company table. Take special notice of the direction of the relationship.

If you are trying to retrofit this into your existing Power BI model you need to ensure that you can do this. For this solution to work the Company selector on the SELECT tab needs to use the “Company_unfilteredCopy” table where previously I was using the “Company” table.

2020-02-10 07_26_06-Industry Selection Fix - Power BI Desktop

The relationship definitions are as follows:

2020-02-10 07_26_57-

2020-02-10 07_27_54-

Two final pieces of the puzzle to make the data present in a matrix.

  1. The DAX – see below
  2. The Matrix setup of the columns – it uses the Company Name and Industry from the original Company table.

 

The DAX:

_Amount_4_Industry_alt =
CALCULATE (
    SUM ( Transactions[Amount] )
    , USERELATIONSHIP ( Company_unFilteredCopy[Industry], Company[Industry] )
    , CROSSFILTER ( Company_unFilteredCopy[Company Name], Company[Company Name], NONE )
)

 

The picture below shows the working solution (on the right). The matrix on the left is the original implementation.

2020-02-10 07_31_02-Industry Selection Fix - Power BI Desktop

The updated pbix file can be downloaded here. https://sites.google.com/site/dgwilson65/Industry%20Selection%20Fix2.zip?attredirects=0&d=1

Good luck on your implementation if you choose to use this.

PowerBI DAX Slicers, Calculate, SUMX 31 January 2020

Posted by David Wilson in General.
9 comments

#PowerBI

I have a scenario that I’ve worked through recently that involved some out of the box thinking and a shallow dive into some DAX and the inner workings of SUMX.

The sample PBIX and datafile can be downloaded from here.

https://sites.google.com/site/dgwilson65/Industry%20Selection%20Fix.zip?attredirects=0&d=1

The user interaction with this Power BI report is to use the SELECT tab to select the Company they want to review some financial transactions for.

In the demo pbix file you can choose a company with the selected company being confirmed by the Measure output (yellow text on the black background).

The Company Name slicer is Sync’d on all subsequent tabs of the report – it’s shown on the SELECT tab and hidden on subsequent tabs.

Each company is associated with an Industry, so the intent is to show financial performance for all companies in the same industry as the selected company.

Picture-1 shows the data loaded to the Company table and which industry each company is associated with.

Picture-1

Picture-2 shows the PowerBI desktop where Company-M has been selected and the measure on screen confirms the selected Company.

Picture-2

Picture-3 shows the next tab where the hidden sync’d slicer restricts the displayed data to the company selected on the SELECT screen. It shows the financial transactions for the company across 5 months presented in a Matrix.

Picture-3

The third tab (Similar Industry) is where I want to display a summary of the financial transactions for all companies in the same industry as the company selected. Picture-4 shows what I want to see. The challenge is that the Sync’d Company slicer restricts reporting on the page to just the company selected in the slicer (i.e. the company on the SELECT tab). To deliver the required results a measure is required that calculates the result required.

Picture-4

What is required here is a Measure that calculates the value for each cell. It is a simple CALCULATE with a SUM and some filtering that lets me see all of the transactions for all companies.

First the Matrix. Importantly the columns Industry and Company Name. If these come from the company table they are restricted to showing only the company from the Sync’s slicer. This does not work for what is needed to be shown. To work around this I created a copy of the Company table called Company_unFiltered. The Matrix shows columns from this table. The unfiltered table has no relationship to any other tables (i.e. established in the datamodel). From the Modelling menu create a new table and use this DAX to create it.

Company_unFiltered = CALCULATETABLE(Company)

Picture-5

Now the DAX measure has the information required to calculate some numbers for us. DAX allows me to determine what the user has chosen in the slicer and further the Industry associated with that particular Company.

VAR industry_local = SELECTEDVALUE(Company[Industry])

And as the Measure calculates for each row and column we need to calculate an amount for each cell. To do that we need to know where we are and calculate that amount accordingly. To understand which row the measure is calculating the ISFILTERED DAX command is used.

VAR myGLCode = SELECTEDVALUE(Transactions[GL Code])

VAR myCompany = SELECTEDVALUE(Company_unFiltered[Company Name])

VAR amount 3 = IF(ISFILTERED(Company_unFiltered[Company Name]),
     IF(ISFILTERED(Transactions[GL Code]),
        CALCULATE(
            SUM(Transactions[Amount]),
            ALL(Company)
            , Company_unFiltered[Industry] = industry_local
            , Transactions[Company] = myCompany
            , Transactions[GL Code] = myGLCode
         )

After the calculation is complete the Measure returns a result. The result is only returned for Companies in the same industry. This means that if there is no data to present the column is not presented in the Matrix.

RETURN
         IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
             amount3
           )

The snippet of DAX presented above will calculate the SUM of transactions for a Company in the same Industry as the selected company and for each of the GLCodes as each row and column in the Matrix is iterated through. This is works well until you come to the total of the column and total of the row. Below is the full DAX measure used to provide the result. Examining it in more detail you can see that the ELSE conditions of the IF statement have an altered filter condition in the CALCULATE that remove the filter of the GLCode.

_Amount_4_Industry =
VAR industry_local = SELECTEDVALUE(Company[Industry])
VAR myGLCode = SELECTEDVALUE(Transactions[GL Code])
VAR myCompany = SELECTEDVALUE(Company_unFiltered[Company Name])
VAR amount3 = IF(ISFILTERED(Company_unFiltered[Company Name]),
       IF(ISFILTERED(Transactions[GL Code]),
            CALCULATE(
                SUM(Transactions[Amount]),
                ALL(Company)
                , Company_unFiltered[Industry] = industry_local
                , Transactions[Company] = myCompany
                , Transactions[GL Code] = myGLCode
                ),
            CALCULATE(
                SUM(Transactions[Amount]),
                ALL(Company)
                , Company_unFiltered[Industry] = industry_local
                , Transactions[Company] = myCompany
                )
          ),
       IF(ISFILTERED(Transactions[GL Code]),
            CALCULATE(
                SUM(Transactions[Amount]),
                ALL(Company)
                , Company[Industry] = industry_local
                , Transactions[GL Code] = myGLCode
            ),
            CALCULATE(
                SUM(Transactions[Amount]),
                ALL(Company)
                , Company[Industry] = industry_local
            ))
        )

return
       IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
             amount3
          )

All of that is rather messy and can be challenging to debug. There’s an easier way! The DAX function SUMX helps us. It is an Iterator function that works through a table Row by Row (refer to the separate note care of Matt Allington on SUM vs. SUMX).

SUMX handles the column totals automatically although management of the column and column totals still needs filtering. The DAX below is a lot less complicated.

[Update 3 February] – Marco Russo posted a comment correctly advising that SUM can be used within the CALCULATE statement. I’ve modified the code below for this. The text around this post has not been updated at this time.

_Amount_4_Industry_SUM =
VAR industry_local = SELECTEDVALUE(Company[Industry])
VAR company_local = SELECTEDVALUE(Company_unFiltered[Company Name])
VAR amount2 = IF(ISFILTERED(Company_unFiltered[Company Name]),
                  CALCULATE(
                     SUM(Transactions, [Amount]),
                     ALL(Company),
                     Company[Industry] = industry_local,
                     Company[Company Name] = company_local)
                  ,
                  CALCULATE(
                     SUM(Transactions, [Amount]),
                     ALL(Company),
                     Company[Industry] = industry_local)
                   )

return
        IF(industry_local IN FILTERS(Company_unFiltered[Industry]),
              amount2
           )

Picture-6 shows the final tab in the demo pbix file and both the results of the SUM and SUMX measures.

You can go back to the SELECT TAB and choose another company, from a different industry, return to this TAB and see the updated report based on the industry associated with the selected company.

 

Picture-6

Credit to Matt Allington

https://exceleratorbi.com.au/use-sum-vs-sumx/

– David

TrafficCamNZ with DarkMode support 7 January 2020

Posted by David Wilson in General.
add a comment

New for 2020 TrafficCamNZ has been updated with the following enhancements and fixes:

● Minimum iOS support level is now iOS 11.0
● Supports Dark Mode and it rocks!
● Fixes an issue where the Camera name got progressively overwritten in the All Camera tab. It is clean and beautiful now.
● Fixes a preferences issue on iPad that prevented the Predictive mode from being turned on
● ChromeCast support upgraded for version 4.4.4 of the ChromeCast SDK to support iOS 13

A note about ChromeCast support
– it requires access to WiFi, Bluetooth, and Microphone – so TrafficCamNZ will request your permission to access these.
– Uses microphone access to listen for ultrasonic tokens when pairing with nearby Cast devices.
– Uses Bluetooth to discover nearby Cast devices.
– Uses WiFi to discover nearby Cast devices – and to access web cameras/traffic camera data for display

TrafficCamNZ will be available on your iOS device shortly via the standard AppStore Software Update.

– David

David Wilson
https://dgwilson.wordpress.com
https://nz.linkedin.com/in/dgwilson65

Take a Moment with macOS 10.15 9 November 2019

Posted by David Wilson in General.
add a comment

My macOS Moments application needs a little bit of refinement to work under macOS 10.15.
For now if you want to run the Moments software you’ll have to stay with macOS 10.14.

Here is the link to Moments in the AppStore https://apps.apple.com/nz/app/moments/id1194414752?mt=12

For those interested in details under the hood… and to assist other developers with some diagnostics.

I’m performing all of my diagnostic work while running macOS 10.15 and testing directly in Xcode.
I encountered issues with the call to “requestOpenSession” and did not receive the downstream delegate call backs to “device:didOpenSessionWithError:”.
I found that changing the deployment target from 10.12 to 10.13 and performing a clean and build now allowed the application to function correctly. The Camera session would open and allowed the application to display images on the camera.

During these diagnostics I’ve been struggling with “requestTakePicture”.
Firstly I lost control of the camera in that I couldn’t;t even use the shutter on the camera to take a picture and through the viewfinder the camera reported “busy”.
With the change in deployment target to 10.13 this seems to have changed in that I can now user the shutter on the camera.
I’m still unable to take a picture triggered by “requestTakePicture”.

Here are the device details, note the device capability “ ICCameraDeviceCanTakePicture” is present:

ICCameraDevice <0x10203cf70>:
delegate : <0x6000039108f0>
deviceRef : 0x10000069
connectionID : 0xad6a3562
deviceID : 0x0200002b
name : Canon EOS Kiss X6i
locationDescription : ICDeviceLocationDescriptionUSB
iconPath : /System/Library/Image Capture/Support/Icons/GenericCamera.png
softwareInstallPercentDone : 100.000000
modulePath : /System/Library/Image Capture/Devices/PTPCamera.app
moduleVersion : (null)
moduleExecutableArchitecture : 0
type : 0x00000101
UUIDString : 00000000-0000-0000-0000-000004A9323B
persistentIDString : 00000000-0000-0000-0000-000004A9323B
autolaunchApplicationPath :
capabilities : ICCameraDeviceCanTakePictureUsingShutterReleaseOnCamera
ICCameraDeviceCanTakePicture
ICCameraDeviceCanDeleteOneFile
ICCameraDeviceCanAcceptPTPCommands
shared : NO
transportType : ICTransportTypeUSB
usbLocationID : 0x14500000
usbProductID : 0x323b
usbVendorID : 0x04a9
usbIntefaceClass : 0x00
usbInterfaceSubClass : 0x00
usbInterfaceProtocol : 0x00
timeOffset : 0.000000
hasConfigurableWiFiInterface : N/A
isAccessRestrictedAppleDevice : NO

I have also tested Deployment Target of 10.15 – this had not had a difference in behaviour.

Next update when I have more to tell.

– David

David Wilson
https://dgwilson.wordpress.com
https://nz.linkedin.com/in/dgwilson65

Power BI – Incremental Refresh – Tip 16 October 2019

Posted by David Wilson in General.
add a comment

#PowerBI

I’ve written before about Incremental Refresh with Power BI – this post looks at error messages that occur as part of the refresh and what I did to solve that.

The key thing here is the perception that Refresh in Power BI Desktop works and Cloud Refresh does not.

My data:

Table 1 – Financial Year 2018 (FY18) – a subset of columns from the main data set – it’s historical data for which there is no further detail.

Table 2 – Financial Year 2019 (FY19) – Lots of transaction detail and 11+ million rows of data growing at 1 million rows per month.

Table 1 is appended to Table 2 in Power Query as follows:

Step 1. Desk refresh full and partial (as per date range parameters) is successful.

Step 2. Upload to cloud service, connect the database up and refresh.

Step 3. Review failure notification.

So good news. Microsoft seems to have recently have enhanced the error reporting for Refresh. You now get the “underlying error message” – this is a huge step forward. The error message doesn’t tell you which table or tables are involved so that remains a challenge for large schemas… at least with some educated guesswork we now have some clues.

What was broken? It looks like definition of the table for FY18 was not compatible with the definition of the table for FY19 – or more specifically – one of the columns. Although that is not entirely true as the refresh worked in the desktop. It is more like something did not correctly align to support Incremental Refresh. Looking at the Incremental Refresh Configuration (see image below) the key fields here are RPT_MONTH and LAST_MODIFIED. With my table merge everything has a LAST_MODIFIED date… although everything from TABLE1 has a blank LAST_MODIFIED date (that might be a problem).

At this point it is easier to show you the related M code. What I have done is add a LAST_MODIFIED column specifically into TABLE1 with a default date. This way I do not end up with null or blank values.

Cloud Service Refresh with this FY18 definition fails

let

Source = Oracle.Database("xxxxxx", [HierarchicalNavigation=true]),
REVENUE = Source{[Schema="zzzzzz"]}[Data],
TABLE1 = zzzzzz{[Name="TABLE1"]}[Data]
in
TABLE1

Cloud Service Refresh with this FY18 definition succeeds

let

Source = Oracle.Database("xxxxxx", [HierarchicalNavigation=true]),
REVENUE = Source{[Schema="zzzzzz"]}[Data],
TABLE1 = zzzzzz{[Name="TABLE1"]}[Data],
#"Filtered Rows" = Table.SelectRows(TABLE1, each [RPT_MONTH] >= RangeStart and [RPT_MONTH] < RangeEnd),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "LAST_MODIFIED", each DateTime.Date(#datetime(2018, 07, 01, 00, 00, 00))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"LAST_MODIFIED", type datetime}})
in
#"Changed Type"

And for reference this is the M code for the FY19 data

let

Source = Oracle.Database("xxxxxx", [HierarchicalNavigation=true]),
zzzzzz = Source{[Schema="zzzzzz"]}[Data],
TABLE2 = REVENUE{[Name="TABLE2"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(TABLE2,{"DATA_SOURCE", "RPT_MONTH", "FIN_YEAR", "FIN_PERIOD", "LAST_MODIFIED", "HEAD_PARTY_NO_CURR", }),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"HEAD_PARTY_NO_CURR", "HEAD_PARTY_NO}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [RPT_MONTH] >= RangeStart and [RPT_MONTH] < RangeEnd),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"HEAD_PARTY_NO", Text.Trim, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"HEAD_PARTY_NO", type number}, {"PROFIT_CENTRE", type text}}),
#"Appended Query" = Table.Combine({#"Changed Type", TABLE1})
in
#"Appended Query"

Save changes, upload to cloud. Refresh.

Success!

– David

Power BI – Time Intelligence – the way I want it 16 October 2019

Posted by David Wilson in General.
add a comment

Sometime the Syntax Sugar of DAX does too much for you making it a struggle to get what seems like a simple result.

I had thought that many problems are solved with the “simple” TOTALYTD function… the challenges begin when you cross a financial year boundary and have partial data for the new financial year yet still want to show last years data as “this year”… and so on. Another challenge is getting a “this year” progress line on a chart that does not then run out horizontally for the remaining months of the year….

This is the result I want:

And here’s the Measure I needed to support this:

_Margin % TY Chart =

VAR reportMonth = [Report_Month]
VAR fiscalYear = [Fiscal Year TY]

RETURN

CALCULATE(
DIVIDE([_Revenue TY] - [_Cost TY], [_Revenue TY]),
FILTER('Date', 'Date'[Fiscal Year]=fiscalYear && 'date'[Date] <= reportMonth)

)

Notice how I’ve used Calculate to wrap this and include a specific filter for Date. I could then apply this to my Cost measure which incorporates TOTALYTD.

_Cost TY Chart =

VAR reportMonth = [Report_Month]
VAR fiscalYear = [Fiscal Year TY]

RETURN

CALCULATE(
TOTALYTD(SUM(EVO_DMT_BUS_CUST_PROFIT_VW[_Cost]), 'Date'[Date], "30 June"),
FILTER('Date', 'Date'[Fiscal Year]=fiscalYear && 'date'[Date] <= reportMonth)

)

As far as Time Intelligence is concerned this was rather gnarly to solve.

Simple in the end… such a heartache when you’re focused on TOTALYTD solving all of the problems of the world.

Any variation of the Cost measure and it breaks!

– David

TrafficCamNZ iOS 13 update 2 October 2019

Posted by David Wilson in General.
add a comment

A quick note to let everyone know that the TrafficCamNZ update for iOS 13 is in development.

The release will also include the latest ChromeCast support version too.
I’ve got something that still needs to be done on the ChromeCast side but all the other changes are done unless I find something else to include.

1.10.1
In this version

This is a maintenance release.

● Minimum iOS support level is now iOS 11.0
● Supports Dark Mode and it rocks!
● Fixes an issue where the Camera name got progressively overwritten in the All Camera tab. It is clean and beautiful now.
● ChromeCast support upgraded for version 4.4.4 of the ChromeCast SDK to support iOS 13

A note about ChromeCast support
– it requires access to WiFi, Bluetooth, and Microphone – so TrafficCamNZ will request your permission to access these.
– Uses microphone access to listen for ultrasonic tokens when pairing with nearby Cast devices.
– Uses Bluetooth to discover nearby Cast devices.
– Uses WiFi to discover nearby Cast devices – and to access web cameras/traffic camera data for display

– David

Power BI – Incremental Refresh – Extra 9 June 2019

Posted by David Wilson in General.
add a comment

#PowerBI #Power BI #IncrementalRefresh #PowerQuery

In a previous post I’ve discussed my experiences with Incremental Refresh:https://dgwilson.wordpress.com/2019/05/19/power-bi-incremental-refresh-and-query-folding/

My implementation is not as clean like the Microsoft examples and Ive encountered some challenges to get it working as per the “label on the box”.

In an Oracle database I have two tables, Financial year (FY) 18 data in one table (55,000 rows) and FY19 onwards data in a second table (18 million rows and growing at 1.5 million rows per month). The FY18 data contains a subset of columns that are in the FY19 table. The tables are separate as the source of the old FY18 data is different and only contains data at a high summary level. The idea is that these two tables are combined in Power Query to present as one table.

The image below shows the Power Query steps for the import of the FY18 data. Note also the “Filtered Rows” step that applies the date range filter for the parameters RangeStart and RangeEnd required for Incremental Refresh. These are the same RangeStart and RangeEnd parameters that are used on the FY19 data table even though the data in this table will never be added to.

The image below shows the Power Query steps for the import of the FY19 data and importantly the Append Query step with brings in the FY18 data. Note also the “Filtered Rows” step that applies the date range filter for the parameters RangeStart and RangeEnd required for Incremental Refresh.

The RangeStart and RangeEnd parameters are showing in the image below. The RangeStart is set to 1/07/2017 so that it covers the FY18 date range. Note that the Financial Year for this data is 1 July to 30 June.

Finally after Power Query is closed and you’re back in Power BI Desktop, set the refresh parameters on both tables.

Save, publish the .pbix file to a Premium Workspace in the cloud and initiate the refresh!

When I was *NOT* applying the Incremental Refresh parameters to the FY18 data the cloud refresh would somehow cause the number of rows in the FY18 data to be duplicated approximately 60 times, so financial totals blew out by a factor of 60 for FY18 for some spectacular results…. not really what was intended. The image below, read right to left, shows how each month of FY18 data has approximately 4,500 rows of data and that after the cloud incremental refresh there us approximately 281,000 rows of data per month for FY18 – which is incremental Refresh going wild.

The trick here is to apply the Incremental Refresh changes to the FY18 data as well as the FY19 data. Even though the FY18 data will never change. You need to apply the change in Power Query and specify the Incremental Refresh parameters in Power BI Desktop.

Reference:
https://community.powerbi.com/t5/Power-Query/Incremental-Refresh-and-a-Merged-Query/m-p/700754#M23483

– David