same date slicer for multiple fields

[AdamEH]

Hey everyone,

I have an issue and i was wondering if an easy solution existed in PowerBi desktop.

I'm trying to get a unique date slicer to control 3 cards and/or graphs on different fields on the same page.

To be more precise : 

I have 3 values i'm interested in :

My number of appointment between  two dates

My number of obtained permits between two dates

My numer of validated projects between two dates

all of those values are in separate tables, and all of these events have a date assicated to them in their tables.

Is it possible for a unique date slicer to on one card act on the appointment_date field, on another card on the obtainment_date of my permit table and on the last on the validation_date of my project table ?

Thanks !

[amitchandak]

You can create a date dimension and join with all three dates. One Active and two inactive. Use userelation to activate other joins in measure.

Refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

But the data is in 3 different tables. Why would he need 2 inactives relationships with USERELATIONSHIP to activate them? He can simply slice by the same date using 3 plain active relationships 

Am I missing something?

[Anonymous]

If the data is structured in that way (3 different tables for the 3 different metrics), you can just create 3 relationship between the Date table and the target tables on the target date column. The date filter will then act on the 3 tables in the same way, filtering the rows in 3 target tables which have a date in the selected period 🙂 

[Anonymous · Accepted solution]

Hello @AdamEH 

It sure is possible, you need to add a calendar table to your model that you connect all the dates to.

This calendar table will filter all the tables that are connected to it.

There are many ways to create calendar tables, but here is a function I provide to all of the people I train:

// PQ Calendar Table by Joren Venema
    // Date: 03-01-2020

(StartDate as date, #"Years ahead(of today)" as number) =>

let
    //Variables used to Generate Calendar.
    EndDate = Date.AddYears( DateTime.Date( DateTimeZone.LocalNow()),#"Years ahead(of today)"),

    //Generate a list of dates between start and end date.
    Source = {Number.From(StartDate)..Number.From(EndDate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    //Adding columns based on Date.
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted QuarterNum" = Table.AddColumn(#"Inserted Month", "QuarterNum", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted QuarterNum", "Quarter", each Text.Combine({"Q",Text.From([QuarterNum])}),type text),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter", "MonthName", each Date.MonthName([Date]), type text),
    #"Capitalized Each Word" = Table.TransformColumns(#"Inserted Month Name",{{"MonthName", Text.Proper, type text}}),
    #"Inserted Day" = Table.AddColumn(#"Capitalized Each Word", "DayOfMonth", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Weekday", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Networkday" = Table.AddColumn(#"Inserted Day of Week", "NetworkDay", each if [Weekday] = 5 or [Weekday]= 6 then 0 else 1),
    #"Inserted YearMonth" = Table.AddColumn(#"Inserted Networkday", "YearMonth", each Text.Combine({Text.From([Year]),"-",if Text.Length( Text.From([Month]))=1 then Text.Combine({"0",Text.From([Month])}) else Text.From([Month]) }),type text),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted YearMonth", "Week", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

Just replace everything in a blank query in the query editor with the code above and use the parameters to generate the calendar table.

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[AdamEH]

@Anonymous Thank you so much for the script. Works like a charm !

@Anonymous It indeed seems to work just by adding a date table with Joren script and linking it to my tables. No need for the USERELATION function.

Thank you very much guys.

Have a nice day.