Careers   |   Events   |   Contact   |   402.238.1399   |   contactus@deliveron.com

Deliveron
Connect with us on FacebookConnect with us on LinkedInFollow Us on Twitter

Category List


Tag List

performance (3)
Java (1)
team foundation server (6)
PBI (1)
action-filters (2)
webs (2)
gulp (2)
association (1)
windows 8 store app (2)
vsts (22)
deliveron agile delivery process (2)
Multi-Factor Authentication (1)
deployment (2)
IntelliSense (1)
DevSecOps (2)
2013 (2)
Mike Douglas (4)
power tools (2)
visual studio 2012 (10)
whitelist (1)
windows azure (2)
lab management 2010 (2)
tips (2)
spc14 (4)
xunit (1)
ssrs (4)
sql server 2008 (2)
nebraska code camp (2)
Keith Holt (1)
Data Analytics (1)
adfs (3)
bundling (2)
selenium (4)
Visual Studio Online (6)
sharepoint 2007 (2)
user profiles (4)
lab environments (1)
bi (2)
mstestv2 (1)
dependency-injection (2)
pipeline (1)
preview (1)
Document (1)
AzureAD (1)
storyboarding (2)
rest assured (1)
example (2)
REST (1)
coded ui tests (6)
team web access (2)
load tests (2)
tfs odata (2)
visual studio (8)
web application firewall (1)
msi (2)
event-handling (2)
MVP (2)
asp.net-mvc-4 (2)
asp.net-mvc-routing (2)
Power BI (3)
test agent (2)
tls 1.2 (1)
Quality (2)
licensing (2)
tfs 2017 (1)
Collaboration (2)
web deploy (1)
react (1)
asp.net-mvc-3 (4)
Analytics (2)
sql saturday (2)
tfs 2012 (4)
Decisions (2)
sql server (2)
reporting (2)
asp.net-mvc (6)
azure resource manager (1)
single page applicaiton (1)
video (2)
dbpro (2)
visual studio code (1)
whitesource (1)
dns (2)
planning poker (4)
team foundation server 2012 (4)
web performance tests (2)
release management (7)
Infrastructure (1)
fields (2)
gherkin (2)
code-first (4)
powershell 2.0 (8)
table-valued-parameters (4)
certificates (2)
ninject (2)
vsdbcmd (2)
business intelligence (2)
Visual Studio 2017 (4)
visual studio 2010 (18)
API Tests (1)
continuous inegration (2)
ssl (1)
Business (2)
deliveron alm delivery guidance (2)
alerts (2)
tfs (11)
webparts (2)
subsites (2)
javascript (3)
Web API (1)
nintex (4)
TFS 2015 (6)
team foundation server 11 beta (2)
Big Data (2)
entity-framework (6)
pipelines (1)
owasp (3)
security (1)
Build (6)
microsoft case study (2)
webpack (1)
installation (2)
service-fabric (1)
wiki (1)
test cases (2)
Functions (2)
netstandard (2)
visual studio 11 beta (2)
jquery (4)
angular 2 (3)
MFA (1)
Azure Functions (1)
cascading-dropdown (2)
Powershell (1)
kanban (1)
team deploy (2)
performancepoint services (2)
work item (2)
continuous integration (2)
Cosmos DB (1)
database projects (4)
team build (2)
tokenization (1)
site collections (2)
Azure (14)
necc (2)
xaml (2)
burndown (2)
silverlight (2)
lunch and learn (2)
alm rangers (1)
requirements (2)
workflow (2)
Nuget (1)
OAuth2 (1)
azure mobile services (2)
faq (2)
zap (1)
business insight (2)
c# (13)
pdf (2)
opensource (1)
sharepoint 2013 (6)
Node.js (1)
application insights (2)
Office 365 (1)
DevOps (14)
angular (1)
insiders (1)
outlook (2)
onenote (4)
Template (1)
Meetings (1)
feedback (2)
CI (3)
Automated Testing (10)
webcast (4)
artifacts (1)
visual studio team services (1)
tags (2)
json (2)
sharepoint 2010 (10)
AAD (2)
test automation (1)
ARM Template (1)
bdd (2)
sp1 (2)
microsoft test manager (2)
nunit (1)
microsoft alm rangers (2)
web (1)
scrum (8)
sharepoint (5)
mvvm (2)
automatedui (1)
fluentvalidation (2)
alm (9)
razor (4)
serverless (3)
agile (12)
github (1)
exchange (2)
TestArchitect (5)
swagger (1)
team foundation 2012 (2)
M Query (1)
wit (2)
tfs 2013 (2)
data warehousing (2)
my work (2)
top 5 (1)
home projects (1)
testing (5)
selinium (1)
test controller (2)
database publishing wizard (2)
tfs 2010 (2)
connect() (1)
lync (2)
HOLs (1)
load testing (2)
ssas (2)
AzureAD Admins (1)
MSBuild (1)

Archive

Using a Single Date Picker with Conditional Filtering in Power BI

Aug 30, 2018

If you have ever wanted to filter a report view in Power BI using a single date parameter with conditional logic, this can be done in one of two ways that I will cover in this post. To set the stage, I am going to be looking at data regarding movie releases. The fields that we will be focused on include: Movie Title, Theatrical Release Date, and DVD Release Date. As a requirement, I want to be able to select a single date and filter all movies that had a theatrical release before the date selected and a DVD release after the date selected. This filter will give me a sense of which movies were likely in theaters at the same time.

 movies_table_view.png

Option 1: Using Query Parameters

This is the more limited of the two options. We start with the table containing the three columns mentioned above. In the Power Query Editor, select Manage Parameters > New Parameter. Enter the following information as shown in the screenshot below.

 selected_date_parameter.png

The key is to set the Type to "Date". You will notice that if you create a parameter on the report itself, there is no option for setting the type to "Date" which is why we create the parameter through Power Query. Once the parameter is created, select to add a custom column to the Movies table. Enter the formula as shown in the screenshot below. In the formula, we are taking the selected data parameter and assigning a value of True or False based on our if logic.

 selected_date_flag_formula.png

From here we can filter to only rows that are set to True and remove the custom column. Now when you update the default value for the parameter, the rows will refresh and reflect the newly selected value. The main drawback to this approach is that it requires you to re-publish the report to the Power BI service every time you need to change the selected date. If you are only working out of Power BI Desktop, this is less of a concern.

 

Option 2: Using DAX

Although option 1 provides a solution to this problem, there is still a better way using DAX. By using this solution, you only need to publish the report once and then the selected date can be changed within the report on the Power BI service.

In order to use this solution we will first need to create a calendar table. We can do this by selecting "New Table" on the Modeling tab of the report. Then pasting in the following DAX.

Calendar =

     VAR BaseCalendar =

         CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2030, 12, 31 ) )

     VAR RenamedCalendar =

         SELECTCOLUMNS ( BaseCalendar, "Calendar[Date]", [Date] )

     VAR Calendar_1 =

         SELECTCOLUMNS (

             RenamedCalendar,

             "Date", 'Calendar'[Date],

             "Year", YEAR ( Calendar[Date] ),

             "Month Number", MONTH ( Calendar[Date] ),

             "Month", FORMAT ( Calendar[Date], "mmmm" ),

             "Week", WEEKNUM('Calendar'[Date])

         )

     VAR Calendar_2 =

         ADDCOLUMNS ( Calendar_1, "Year Month Number", [Year] * 12 [Month Number] - 1 )

     RETURN

         Calendar_2

Once the calendar table has been created, select to create a new measure. The measure will be called "Selected Date". The formula for selected date is as follows:

     Selected Date = CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )

Next we need to create a measure out of each date field in the Movies table.

     Theatrical Release Date = MIN('Movies'[Theatrical Release])

     DVD Release Date = MIN('Movies'[DVD Release])

Once those measures have been created, one more measure will need to be created to handle the conditional logic.

Conditional Date = IF([Theatrical Release Date]

                      && [DVD Release Date] > [Selected Date]

                       ,"T", "F")  

Now the all the measures are in place, we can start adding the visuals to the canvas. We will first add a slicer and set the field to "Date" from the Calendar table. The slider type should be set to "Before". Then on the slider format, select to turn off slider. Now select the table visual. Add the Movie Title, Theatrical Release, and DVD Release values to the table. In the Visual level filters, add the Conditional Date. Set the Conditional Date filter value to True.

 report_canvas_with_picker.png

There is now a single date picker that will dynamically filter the table based on the date selected. As I mentioned previously, this approach is much better if you are publishing to the Power BI service compared to the first option since updating the filter is more direct and intuitive. If you have any questions or comments, please reach out to me on Twitter.



Category: Business Analytics

Michael Parisen


We believe in helping our customers create software solutions in a better way.
We do this by having a project delivery process and technology expertise that ensures we are solving the right problem in the right way and driving the most business value.