Forum Discussion
dynamic dimension
Hi Emmy66
From your initial description, you may want to show the count of Full Time Employees and Part Time Employees by measure.
I think in your fact table it should have a column like type of employee to let me distinguish full time or part time employees.
And there should be a column or multiple columns with values in Metric and details like United Kingdom/Australia... in Country.
However I couldn't find them in your data model.
I need more details about your table. Or you can share a sample with me by your Onedrive for Business.
Could tell me your calculate logic of your measure?
Does Active_Visitors calculate the count of full time employee?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous for your continued assistance. Apologies if I didnt provide you with enough information. I would have loved to share my data but wont be able to do so due to the nature of my data. I will try as much as possible to provide you all the required information.
User table has employee_type field to differentiate between part time and full time. Also, the user table contains these columns: Country, Department, Role, Office and PracticeArea, Last_logon etc. Below is a sample of my Metrics table.
My 1st measure Overdue Projects. A project is assumed over due if it exceeds it's target date and the variable Selecteddate is dependent on the date selected from the slicer.
My 2nd measure Active Visitors looks at users that have logged on in the last rolling 3 months. this is dependent on the date selected from the date slicer.
Hi Emmy66
I build a sample to have a test.
Is your User Table as below?
You can select Country,Department,Role, Office and PracticeArea, then select unpivot.
New table is as below.
My Metric Table.
My Date Table:
Date = CALENDAR(DATE(2020,01,01),DATE(2020,12,31))Measure:
Count = VAR _Slicer = SELECTEDVALUE ( Metric[Slicer] ) VAR _MaxDate = SELECTEDVALUE ( 'Date'[Date] ) VAR _MinDate = EOMONTH ( _MaxDate, -4 ) + DAY ( _MaxDate ) RETURN CALCULATE ( COUNT ( User[User id] ), FILTER ( User, User[Last_logon] >= _MinDate && User[Last_logon] <= _MaxDate && User[Attribute] = _Slicer ) )Build a Matrix visual. It will show last 3 month rolling count user id by slicers.
Select Country:Select Department:
You can download the pbix file from this link: File
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous please accept my sincere apologies for the late response. Thank you very much for your proposed workaround. Yes the sample table you built above is inline with what I have.
I followed the steps as you advised but I ran into a hitch. When I got to upivot only selected columns and applied the changes, below is the error message generated. Then I tried to isolate the 1st userid that threw the error but another one popped up. So not sure what to do next.
I've checked my table and there are no duplicates.
Hi Emmy66
Your error is caused by there will be duplicate values in uid after you click apply changes which has a one to many relationship currently.
You may delete the relationship(one to many), then apply changes in power query and build a many to many relationship between uid in User and other tables.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous, thanks for your continued support. The User table is joined to many other tables, so, I'm not sure how to proceed with the many-to-many and the impact it will have