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 

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. 

Emmy66's avatar
Emmy66
Helper V
5 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

     

  • Emmy66's avatar
    Emmy66
    Helper V
    5 years ago

    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