Forum Discussion

Emmy66's avatar
Emmy66
Helper V
5 years ago

dynamic dimension

Hi,  Below are some of the steps I've taken and got stock in step 3   Step 1 - I created a Metric Selection table as per below   Step 2 – I created the measure: Metric Selected = SELECTEDV...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Emmy66 

Could you show me a sample like the table you are dealing with?

I need to know your data model and your calculate logic about your measures.

This may make it easier for me to help you.

 

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. 

 

Emmy66's avatar
Emmy66
Helper V
5 years ago

Thanks Anonymous for your reply. Much appreciated. I hope the information below will help. The measures I included are currently calculated from 2 tables in the model (Object & User table) 

Sample measures: There are 5 other measures but the follow similar rule.

These are all based on the date slicer selection

Overdue_Projects =

VAR Selecteddate = SELECTEDVALUE(Dates[Date])

RETURN

    CALCULATE(

        COUNT(Object[proj_id]),

            FILTER(Object, Object[target_date] < Selecteddate),

         )

Active_Visitors=

VAR MinSelectedDate =DATEADD(ALLSELECTED(Dates[Date]),-3,MONTH)

VAR MaxSelectedDate = SELECTEDVALUE(Dates[Date])

 

RETURN   

    CALCULATE([Users[Id]

        FILTER(User, User[last_login]>= MinSelectedDate && User[last_login]<=MaxSelectedDate)

         )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

     

     

     

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      Thanks  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.

    • Emmy66's avatar
      Emmy66
      Helper V
      5 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.