Calendar connected to several date columns in dataset

[ksab23]

Hi all, 

I cannot figure out how to make calendar slicer work in this scenario. I want to connect complete calendar to dataset with several calendar columns.

This is my dummy dataset, where '1' stands for program completion, date is completion date. Except for 'user', all are calculated columns taken from core dataset. 

user	program 1	program 1 date	program 2	program 2 date	program 3	program 3 date
a	1	1/1/2024	1	1/1/2024	1	1/1/2024
b	1	1/2/2024	1	1/2/2024
c	1	1/3/2024	1	1/3/2024	1	1/3/2024
d			1	1/4/2024	1	1/3/2024
e			1	1/5/2024
f					1	1/5/2024

Now, I wanted to connect a calendar dataset that is complete calendar (not just days users completed programs). But I cannot seem to make it work even if I connect the calendar to all 'date' columns. It seems to do something but I always get the total count wrong.

Do you guys have any idea on how to make this work? Thanks a lot!

[v-xuxinyi-msft]

Hi @ksab23 

If I understand correctly, you are trying to filter the table by the calendar table.

Perhaps the following will help.

The following operations are performed in Power Query.

1. Unpivot Columns by selecting the three columns shown in the figure

2. Do this again, selecting the three columns shown for Unpivot Columns

3. To do some organizing, I deleted the "Attribute.1" column and renamed some columns.

Close & Apply

My calendar table

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ksab23]

Thanks! 

This would be a great solution, however, my columns are calculated ones, and unless I am missing something I cannot edit them in power query no?

I have to get data to my user list dataset because the program completion dataset contains only users who completed specific program, not those who didn't. So basically 'user' (user list) is the core data and I am pulling information from several other 'completion lists' to get calculated columns.

[ksab23 · Accepted solution]

Hi, I found a solution in the meantime! Tutorial on youtube but it basically uses 'userelationship' function and it works like a charm.