I have a main table (Please refer TABLE 1): There are multiple Login Dates and Logout Dates for a single Generic Number (they overlap as well and have NULL as the logout date for few cases which means the generic number is still logged in)
TABLE 1
I have created a dimDate Table (taking reference from: Link ) - basically created a calendar table where each date from february 1st 2023 is one row till end of year (specific to current date (April 17th 2024)) - Dec 31st 2024. Multiple columns like Year, Quarter Number, Month, Day, ... Please refer to DIAG 2 (which still goes on till Dec 31st 2024 for now):
DIAG 2
I have created a matrix (Taking the help of video: Link) - Rows: generic number, login date & logout date (all from Main table). Columns: Month & Date (from dimDate table). Values: Measure. I have filtered it to year: 2023 and quarter 'Q1'.
DIAG 3 (MATRIX - with all 3 in the rows)
DIAG 4 (Muasure code)
Now in the matrix(DIAG 3) I can see all the login dates, logout dates and dates between the login and logouts associated '1' (changed it to show a color instead) to it. I was able to do this by using the measure (DIAG 4).
Now in DIAG 5 we just included generic no. and login date in rows due to which for a generic no. & login date we can see '1' for all login dates, latest (logout date) associated with a login date & dates between login and latest (logout date).
As per DIAG 6, DIAG 7 & DIAG 8: Only generic number in rows, where we can only see blue highlight ('1's) for earliest(login date), earliest(logout date), dates between earliest(login) and earliest(logout), latest(login date), latest(logout date) and dates between latest(login) and latest(logout). Whereas, I want to see blue highlight ('1's) specific to a generic number for all login dates, logout dates and for dates between login date and logout date as highlighted (LIKE DIAG 9 - if measure used then '1's will be shown in place of blue color(Just formatted)). Not understanding how to do it, Can somebody please help me? Maybe changing the measure code (OR) being able to do this in a different visualization. I have tried to do this in the in-built GANTT given by PowerBI but just 1 login, logout and days between these dates are shown but not ALL logins, logouts and for dates between them.
DIAG 5 (When we go up a level in rows: by just including generic number & login date)
DIAG 6 (Just generic number being shown in rows; year: 2023; removed quarter for this)
DIAG 7 (right scroll of matrix from DIAG 6)
DIAG 8 (year 2024)
DIAG 9 (How I want the output to look like in PowerBI for all dates; below just showing for year: 2023 and Months: Feb & March)
Hi @srija ,
You can click here:
If you can't upload a pbix file, maybe the file size is too large and exceeds 47MB
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The Main table and dimDate table have the following relationship:
