dynamic dimension

[Emmy66]

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 = SELECTEDVALUE('Metrics Selection'[Metric],"Country")

Step 3 – I tried creating the Selected Metric = SWITCH(TRUE(),

[Metric Selected]= "Department",,

I got stock at the 3rd step which I hope someone can help me figure out the next steps and how I can achieve my goal as explained below.  

My aim is to create a slicer as below using the Metric column highlighted above and based on the Metric Selected in step 2

Intended Output

Department	Full Time Employees	Part Time Emp
Admin	67	15
HR	12	4
Legal	18	10
Accounts	16	5
Finance	41	3

Above is my intended output. So each time I select any of the metrics in the slicer, I will expect the dimension to be changed and displayed on the first column as above. When I select Department, I expect a full list of all the departments against the measures Full time & Part time. Same output expected as below when I select country from the slicer to show all the corresponding measures.

Country	Full Time Employees	Part Time Emp
United Kingdom	80	2
Australia	8	8
USA	9	20
Mexico	10	8
Finance	30	16

[TomMartens]

Hey @Emmy66 

here is a pbix file that contains sample data, there is a report page called "dynamic axis content".

The Table visual and the Stacked bar chart visual are using the column "Axis Label" from the table "Axis content".

The Slicer is using the column "Slicer".

The table is based on a DAX statement, which means whenever the underlying data model will be refreshed the content of the table will be updated as well.

The table FactOnlineSales contains a measure called "dynamic axis measure" this measure maps the axis content to the "real" columns, the "real" tables in your data model.

There is another maybe the goriest detail that makes this approach work. This detail is called lazy evaluation. The evaluation of a condition in DAX expressions is immediately stopped when the whole condition (ANDs and ORs are used) can not become true anymore or the whole expression already is true.

When the slicer selection results in Product Category, the variable checkContinent equals zero. This means that the 1st part of the condition for the continent filter part already stops the evaluation.
The downside of this approach is that each value of the slicer needs its own filter expression.

Hopefully, this provides what you are looking for, at least provides some new ideas.

Regards,

Tom 

[Emmy66]

Hi @TomMartens thanks for the suggestion and also taking the time to assist. I have gone as far as creating 2 columns using the AddColumns/UNION in your pbix file and similar to the layout by @Anonymous but my challenge is how to get the measures into the table. FullTime Employees and PartTime are all individual calculated measures and I've got 7 measures in total to add to the table. Any idea how I can accomplish this please. 

[Anonymous]

Hi @Emmy66,

Tom provides a cool workaround for this problem. Another alternative is to use bookmarks to create different views of the page, where you display different tables based on your selected bookmark. Essentially this means placing a different copy of the table in each bookmark with the categories you want to display, whilst hiding the "unused" tables. You can then toggle which bookmark to display through for example a button with an action.

It can be a little fiddly to set up but it's quite straight forward. This video is a few years old but the process is pretty much the same:

https://www.youtube.com/watch?v=_Afcj8mT5_Q&feature=emb_logo

Hope this also sheds some light on potential solutions!

[Emmy66]

Thanks @Anonymous for your suggestion. Much appreciated. Though the link was very helpful but it's not exactly what I wanted. 

[Anonymous]

Hi @Emmy66 

In addition to  TomMartens 's reply, I think you can use a measure to filter your data table.

Due to I don't know about your data table, I build a sample to have a test.

Sample Table:

Metric Selection table:

Measures:

Metric Selected = SELECTEDVALUE('Metric Selection'[Metric],"Country")

Filter = 
SWITCH(TRUE(),MAX('Table'[Category])='Metric Selection'[Metric Selected],1,0)

Build a table visual, add Filter Measure into Filter Field and set it show items when value =1.

It will show country by default.

If you select Department:

If this reply still couldn't help you solve your problem, please show me your data table and more details about your requirement.

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]

Hi @Anonymous , thanks alot for your proposed solution. As I mentioned in my reply to @TomMartens , I've recreated the table with the 1st 2 columns but stock with how to get the measures in.  Any further details on how to go about it will be highly appreciated. 

[Anonymous]

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]

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]

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]

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]

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]

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]

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]

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

[Emmy66]

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.

[TomMartens]

Hey @Emmy66 ,

Unfortunately it it not that simple to create a solution that will meet your expectation.

This is because of the following.

A visual has a "structure", sometimes this structure is called aesthetics.The structure defines the what column is used on the x-axix, what column is used as legend, etc. The structure of a visual can not be changed using DAX.

This means there is no way to "switch" the column that is used in your visual, based on the slicer selection

Solving the problem requires some kind of trick.

Create a table with two columns, one column that contains the content used inside the slicer, and the other the values from the different columns.

Now you can use the second column as axis or column inside the table visual.

You have to create measure that helps to filter the values column accordingly.

You have to create a measure that is using the axis value to filter the different columns and aggregate your values.

The more "variables" you are using the "longer" the measure will get, as each column needs it's own segment in the measure.

If you need more guidance, please create a pbix file that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[Emmy66]

Hi @TomMartens , thanks for responding to my question. From your explanation, there isn't an easy approach to it. I'm not sure how I can accomplish the table as you suggested since my measures are calculated on the fly.

I might result to using them as individual slicers rather than combining all the demensions as another option. 

Thank you once again for your suggestions.