Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andrew17
Helper I
Helper I

Select "Groups" of Measures in a Slicer

Hi,

 

I have data in a format similar to below: 

 

Unique IDCurrent StatusStatus 1 StartStatus 2 StartStatus 3 StartStatus 3 FinishStatus 1 TimeStatus 2 TimeStatus 3 Time
AA13-Mar-20      
AB211-Mar-2016-Mar-20  5.00  
AC211-Mar-2018-Mar-20  7.00  
AD24-Mar-2010-Mar-20  6.00  
AE24-Mar-2011-Mar-20  7.00  
AF32-Mar-209-Mar-2016-Mar-20 7.007.00 
AG31-Mar-2014-Mar-2020-Mar-20 13.006.00 
AH420-Feb-201-Mar-209-Mar-2020-Mar-2010.008.00

11.00

 

The relevant info from this is the average amount of time spent per unique ID per status over a time period, and also the max time spent per unique ID per status, and I am trying to display the metric for each status at the same time, but also have the ability to toggle between the two measures. 

 

Is there a way I can create a measure for the average of each status (so a "Avg_1, Avg_2, Avg_3, Max_1, Max_2, Max_3") and group them together in a way that I can create a slicer where I can select "Average" and it would update all of my charts to show the average measures, and if I select "Max", it would do the same for the max measures.

 

For example, if I had a card for status 1, status 2 and status 3:

 

If I selected "Average":

Status 1: 7.85

Status 2: 7.00

Status 3: 11.00

 

If I selected "Max":

Status 1: 13.00

Status 2: 8.00

Status 3: 11.00

 

And then in the end, I would display these numbers as a product of time where I have 3 charts showing the trend of each status, and I can toggle between the average and max for each of the 3 statuses over time on 3 different charts.

 

Please let me know if this is possible, and if I explained well enough.

 

I appreciate your help 🙂

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @Andrew17 ,

 

You could create a slicer table at first. Then create three similar measures to show the result.

Measure 1 =
VAR a =
    SELECTEDVALUE ( Slicer[Column1] )
VAR b =
    MAX ( 'Table'[Status 1 Time] )
VAR c =
    AVERAGE ( 'Table'[Status 1 Time] )
RETURN
    IF ( a = "MAX", b, c )

Then use card visual. Here is the result. You could also use bookmark to switch your results. 

1-1.PNG

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

Hi @Andrew17 ,

 

You could create a slicer table at first. Then create three similar measures to show the result.

Measure 1 =
VAR a =
    SELECTEDVALUE ( Slicer[Column1] )
VAR b =
    MAX ( 'Table'[Status 1 Time] )
VAR c =
    AVERAGE ( 'Table'[Status 1 Time] )
RETURN
    IF ( a = "MAX", b, c )

Then use card visual. Here is the result. You could also use bookmark to switch your results. 

1-1.PNG

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Jamesmathew
Helper I
Helper I

Hello Andrew,

      To achieve it, I created a new table as below.

 

Capture1.PNG

Then Created Measures for each status and assigned those measure values to the above table as a new calculate column.

 

Capture2.PNG

 

Capture3.PNG

 

Then, used this table in the chart with Type as Slicer.

 

 

Attached the link for solution below.

https://1drv.ms/u/s!AlTyyZ_9D_WjimMgrhfU3pGS-hbq 

 

Thanks,

James

 

 

Hi @Jamesmathew ,

 

Thanks for your response, this is definitely pushing me in to the right direction!

 

I'm trying to get this solution to show the trend of the data (based on the date fields) Is there a way to manipulate the data to show a line chart with the date in the x axis? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.