Date is not properly filtering 1 of the tables

[h4ck3ff]

Hi guys!,

Im really in a head bang with the following "problem". I did a lots of things but really without success.
Here is my issue:

I have two tables:
1. Event Activity Log (which keeps the data for reports for specific periods - so not all of the reports will be shown there).

2. Table named Reports (which keeps all the Reports, doesnt matter if they are used or not). 
They both are connected by ReportId and everything works fine but when I try to use the DateFormat (date column) filter on Reports - nothing happens. I want to see which reports have activities for specific date but really Im not able to do it. I was trying with measures, bi-directional connection (which only appears to show only the coresponding ReportIds in the both tables), tried to use "show items with no data" on DateFormat column but without success... Could you please suggest me how to proceed? Im really out of "mana". Thanks!

[iceparrot]

Hey  @h4ck3ff , 

I would suggest you to create a calculated dim_date table which consists of two column (dateID and date). 

dim_date = DATESBETWEEN(
    Event Activity Log[<yourDateColumn>]
    , min(Event Activity Log[<yourDateColumn>])
    , max(Event Activity Log[<yourDateColumn>])
)

 Create the dateID column as:

dateID = Format(dim_date[<yourDateColum>], "YYYYMMdd")

Then add a dateID colum to both of your tables using the adapted DAX code for the dateID column. Afterwards create the  bidirectional relationships. Also activate the bidirectional relationship between your Event Activity Log table and the Reports table again.

For your slicer use the date column of your dim_date table. Afterwards it should be possible to filter your reports accordingly.

[h4ck3ff]

I dont have the necessary Date column in Reports table and I was not able to make the connection between the dates;( Im really out of ideas

[h4ck3ff]

@iceparrot , I just create a measure: 

Date = MINX(FILTER('Event Activity Log', 'Event Activity Log'[ReportId] = 'Reports'[ReportId]), 'Event Activity Log'[CreationTime])
and it is working just fine with filtering the results but the only problem here is that the dates are only the ones that already has activities. I want to see for example dates from 2020 till 2025, even there is activities or not and maybe it will work.. 

[iceparrot]

Hi @h4ck3ff , 
it's not working with a measure in this case.

You need to create a calculated table which contains all date values. 

1. Switch to the Data View in Power BI Desktop

2. In the top menu switch to Table tools and select New table

3. For your date_table you can use the following code:

date_table = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))

It will create a table starting from Jan 1st. 2020 until Dec 31th 2025. 

4. You can then create a dateID column again

dateID = Format(date_table[Date], "YYYYMMdd")

5. Create the bidirectional relationships

6. Use the Date column from date_table for your slicer. Then you also have dates in your filter which are not in your activity log. 

[h4ck3ff]

Hi @iceparrot and thanks for your time. Now the slicer is working but the problem now is when there is bi-directional between Reports and Event Activity Log instead of all unused reports (in my case 548) it connect only with the reportid on both tables where Reports.ReportID = EventActivityLog.ReportId and total reports from 548 are now 42.

[iceparrot]

@h4ck3ff so you want the report count 42 still show 548 or the actual number of used reports?

[h4ck3ff]

My used reports are 42, my unused reports are 506. The total count is 548. When I made the bi-directional connection all of my unused reports (they are unused and thats why there are no dates coresponding to them) dissapeared from my report and only the used reports (with dates coresponding to them) are shown. 

Im using the following calculated column to obtain also "Yes" or "No" for the used vs unused reports:

IsUsedId = if(ISBLANK(LOOKUPVALUE('Event Activity Log'[ReportId],'Event Activity Log'[ReportId],'Reports'[ReportId])),"No","Yes")

Again with bi-directional connection the reports with "No" are missing because there is no match between their IDs:

When the bi-directional connection is removed Im able to see also the unused reports which are 506:

I want somehow to keep the data for the unused reports VS used reports

[Dhairya]

Hey @h4ck3ff 
You can create a separate date table using CALENDAR() or CALENDARAUTO() function and use fields from there for all calculations and for plotting slicer.

If it works for you please mark my solution as accepted!

[h4ck3ff]

Hi @Dhairya , I've tried but still without results...