Different measure selectors on diagrams

[tvadoc]

Hello,

The dataset is about surveys where PID is assigned to one study, and cycle time is a difference between 2 milestones expressed in days/weeks/months. In some cases, the survey started within that study (PID), but hasn't finished, hence the null values:

Basically, I'd like to make 2 slicers that changes the values on that diagram based on my selection.

First is a Measure selector, where it shows either Average, Minimum or Maximum

Second is a Date selector, where it shows either Cycle Days, Cycle Weeks or Cycle Months:

So far, what I did, is I created lot of switch cases, where it looks to see what is your choice in the time span and measue selector selection.

Time span selection = SWITCH( TRUE(),
AND(VALUES('Time span selector'[Time span selector]) = "Day", VALUES('Measure selector'[Measure selector]) = "Avg"), AVERAGE(final[Cycle time - DAYS]),
AND(VALUES('Time span selector'[Time span selector]) = "Week", VALUES('Measure selector'[Measure selector]) = "Avg"), AVERAGE(final[Cycle time - WEEKS]),
AND(VALUES('Time span selector'[Time span selector]) = "Month", VALUES('Measure selector'[Measure selector]) = "Avg"), AVERAGE(final[Cycle time - MONTHS]),
AND(VALUES('Time span selector'[Time span selector]) = "Day", VALUES('Measure selector'[Measure selector]) = "Min"), MIN(final[Cycle time - DAYS]),
AND(VALUES('Time span selector'[Time span selector]) = "Week", VALUES('Measure selector'[Measure selector]) = "Min"), MIN(final[Cycle time - WEEKS]),
AND(VALUES('Time span selector'[Time span selector]) = "Month", VALUES('Measure selector'[Measure selector]) = "Min"), AVERAGE(final[Cycle time - MONTHS]),
AND(VALUES('Time span selector'[Time span selector]) = "Day", VALUES('Measure selector'[Measure selector]) = "Max"), AVERAGE(final[Cycle time - DAYS]),
AND(VALUES('Time span selector'[Time span selector]) = "Week", VALUES('Measure selector'[Measure selector]) = "Max"), AVERAGE(final[Cycle time - WEEKS]),
AND(VALUES('Time span selector'[Time span selector]) = "Month", VALUES('Measure selector'[Measure selector]) = "Max"), AVERAGE(final[Cycle time - MONTHS]),
BLANK())

And what I'd like to achieve, is a diagram, where it shows something like this:

A clustered column chart that shows the difference for each surveys based on time span and measure selection.

How should I do this? Is the code I written seems okay?

[v-rongtiep-msft · Accepted solution]

Hi @tvadoc ,

I have created a simple sample, please refer to it to see if it helps you.

Time span selection =
VAR _DAYMAX =
    MAXX ( ALL ( 'Table' ), 'Table'[cycle time-days] )
VAR _DAYMIN =
    MINX ( ALL ( 'Table' ), 'Table'[cycle time-days] )
VAR _DAYAVERAGE =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[cycle time-days] )
VAR _WEEKMAX =
    MAXX ( ALL ( 'Table' ), 'Table'[sysle time- weeks] )
VAR _WEEKMIN =
    MINX ( ALL ( 'Table' ), 'Table'[sysle time- weeks] )
VAR _WEEKAVERAGE =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[sysle time- weeks] )
VAR _MONTHMAX =
    MAXX ( ALL ( 'Table' ), 'Table'[cycle time- months] )
VAR _MONTHMIN =
    MINX ( ALL ( 'Table' ), 'Table'[cycle time- months] )
VAR _MONTHAVERAGE =
    AVERAGEX ( ALL ( 'Table' ), 'Table'[cycle time- months] )
RETURN
    SWITCH (
        TRUE (),
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Day",
            VALUES ( 'Measure selector'[Measure selector] ) = "Avg"
        ), _DAYAVERAGE,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Week",
            VALUES ( 'Measure selector'[Measure selector] ) = "Avg"
        ), _WEEKAVERAGE,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Month",
            VALUES ( 'Measure selector'[Measure selector] ) = "Avg"
        ), _MONTHAVERAGE,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Day",
            VALUES ( 'Measure selector'[Measure selector] ) = "Min"
        ), _DAYMIN,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Week",
            VALUES ( 'Measure selector'[Measure selector] ) = "Min"
        ), _WEEKMIN,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Month",
            VALUES ( 'Measure selector'[Measure selector] ) = "Min"
        ), _MONTHMIN,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Day",
            VALUES ( 'Measure selector'[Measure selector] ) = "Max"
        ), _DAYMAX,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Week",
            VALUES ( 'Measure selector'[Measure selector] ) = "Max"
        ), _WEEKMAX,
        AND (
            VALUES ( 'Time span selector'[Time span selector] ) = "Month",
            VALUES ( 'Measure selector'[Measure selector] ) = "Max"
        ), _MONTHMAX,
        BLANK ()
    )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.