Filtering issue

[Anonymous]

Dear All, 

I have two different Salesforce tables Opportunities and Tasks, each one with a Creation date field. 

On my report, I'd like to have 2 cards, one with the total number of opportunities, and the second one with the total number of tasks. 

I also would like to have a month slicer and I have selected the Creation Date field of the Opportunity table.

hte thing is: when I select a month, only the Opportunity card is adjusting. The Task card remains with the total number of tasks. 

Is there a way to have a time slicer that will adjust both cards? 

thank you 

Esme in Paris

[Anonymous]

Dear Paul, 

many thanks for your answer. 

I'm not sure I followed your procedure correctly:(

1. I created the new Date table and copied pasted the formula. 

when I look at the table, dates are organised hierarchically from Jan 1st, 2002 until Dec, 31st 2028. 

2. then I created the relationships :

- the first table I selected is the Opportunity table and highlighted the Creation Date field

- then I selected the new table and highlighted the Date field

- Cardinality : Many to one and Cross filter direction: single

Can you confirm that I did not make any mistake? 

the reason I ask you for this validation is that when I create a table on my report (using the Table visual), and selected the orginal Creation Date and the new table Date fields, the Date field remain empty. 

thank you very much Paul

Esme in Paris

[grantsamborn]

Hi

It looks like there is a problem with the relationship since in your date table, every date is displayed as "12:00 AM" while your Creation Dates include a time portion.

As a workaround, you could create a calculated column like this and then use that in the relationship.

CrDt = CONVERT( CONVERT('CreationDates'[Creation Date], INTEGER), DATETIME)

[Anonymous]

Hi Grantsamborn

thank you for your help. 

The Creation Date in my original tables are in this format : 

and the date of the Date table Paul proposed appears this way: 

they both seem having a similar format, don't you think? 

[grantsamborn]

They are the same format but in order for a relationship to work, you need an exact match.  By using that calculated column in the relationship, you get exact matches.

If you need to calculate a duration using the time portion (or something like that), simply use the original "Creation Date".

[Anonymous]

Hi Grantsamborn, 

It seems to work 😉

It is quite late in Paris and I need a sleep. I will test and come back to you tomorrow. Thank you very much and talk to you tomorrow. 

Esme

[PaulDBrown · Accepted solution]

As @grantsamborn rightly points out, the columns in the relationship must be of the same type. If you are working with a Date Table, you must set the Date field in the Date Table and your fact tables as type Date (not Date Time)

The next issue has to do with the level of granularity you need in your model. The field in your data is in fact type Date Time. You need to decide if the time aspect is relevant to your analysis, and if so, you need to vary your model accordingly. If you need the Time breakdown, it is recommended you split the field in your data into a date column and a Time column (you need to decide if the granularity need to be at hour level, Hour:Minute or Hour: Minute: Second level)

Based on this decision, it's easy transform the data in power Query. Simply select the DateTime Field, and addcolumns for date and time using the inbuilt options in Power Query. Remember to change the data type to the appropriate type before you load the data:

 If you only need the granularity at date level, only create the date field.

Once you've made these transformations, you can actually delete the original column since you (probably) won't need it.

If you have kept the time column, you now need a Time Dimension table. This can easily be created in Power Query using the following code in a new blank query:

For granularity down to seconds:

let
    Source = List.Times(#time(0, 0, 0), 24*60*60, #duration(0,0,0,1)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}})
in
    #"Changed Type"

For granularity down to minutes:

let
    Source = List.Times(#time(0, 0, 0),24*60, #duration(0,0,1,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Time in Miutes"}})
    
in
    #"Renamed Columns"

For granularity in Hours :

let
    Source = List.Times(#time(0, 0, 0), 24, #duration(0,1,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Time"}})
in
    #"Renamed Columns"

You can change the Time data type for the fact tables to show only hours in Power Query by selecting the Time field under Transform, and under hour select "Start of hour",

 It is recommended that as many transformations as possible be down in Power Query (or ealier in the source data). However, here is the code to create the same tables using DAX (remember to chage the data type to time once the tables are loaded)

DAX for a new table for time down to seconds:

DAX Time in Seconds =
VAR _hours =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "@Hour", [Value] )
VAR _minutes =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Minutes", [Value] )
VAR _seconds =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Seconds", [Value] )
VAR _MinSecs =
    CROSSJOIN ( _minutes, _seconds )
VAR _HoursMinsSecs =
    ADDCOLUMNS (
        CROSSJOIN ( _hours, _MinSecs ),
        "@TIME", TIME ( [@Hour], [@Minutes], [@Seconds] )
    )
RETURN
    SELECTCOLUMNS ( _HoursMinsSecs, "Time", [@TIME] )

DAX for a new table for time down to minutes:

DAX Time in Minutes =
VAR _hours =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "@Hour", [Value] )
VAR _minutes =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "@Minutes", [Value] )
VAR _HoursMins =
    ADDCOLUMNS (
        CROSSJOIN ( _hours, _minutes ),
        "@TIME", TIME ( [@Hour], [@Minutes], 00 )
    )
RETURN
    SELECTCOLUMNS ( _HoursMins, "Time", [@TIME] )

DAX for hour granularity:

DAX Time in Hours =
VAR _hours =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "@Hour", [Value] )
RETURN
    SELECTCOLUMNS ( _hours, "Time", TIME ( [@Hour], 00, 00 ) )

Once the tables are loaded, and once the columns are all in the correct data type, you can create the single direction relationships between the dimension tables and the corresponding fields in the fact tables.

I hope that helps!

I've attached the sample PBIX file I used for this example for your reference

[PaulDBrown]

You need to create a date table to use as a dimension table for the Date fields. Under Modeling in the ribbon, select "New table" and type in:

Date Table =
ADDCOLUMNS (
    CALENDARAUTO (),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

Next go into the modeling view and create a single direction relationship from the Date Table[Date] field and each date table in the fact tables. Finally select the Date Table in the field pane and mark it as the Date Table in the ribbon.

You now use the fields in the Date Table for slicers, filters, measures and visuals.

If you have any other fields which are common to both tables, you should also create dimmension tables with unique values for these fields.