Forum Discussion

Frequent Visitor

3 years ago

Solved

Creating a table with values changing respective with what if parameter.

I am creating a Dashboard with two what-if parameters. Able to create a Scatter plot with Quadrants, changing with respective what-if parameters As shown in the picture. The Quadrants will...

Need Help

Anonymous

3 years ago

Hi karthikmiriyala,

You can create a new table with two modes to use in formula change calculation mode:

SwitchMode = {"Mean","Median"}

Here are the dynamic measure formulas based on two what-if parameter tables, Quadrant group and mode slicer.

petalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

petalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

sepalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

sepalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

Regards,

Xiaoxin Sheng

sample.pbix37 KB

Anonymous

Not applicable

3 years ago

Hi karthikmiriyala ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

karthikmiriyala

Frequent Visitor

3 years ago

Hi Anonymous,
Thank you very much for the reply, Please find the link for the dataset: https://gist.githubusercontent.com/curran/a08a1080b88344b0c8a7/raw/0e7a9b0a5d22642a06d3d5b9bcbad9890c8ee534/iris.csvhttps://gist.github.com/curran/a08a1080b88344b0c8a7

these are the fields

sepal_length,sepal_width,petal_length,petal_width,species

in the dataset.

I am trying to create a dashboard that will take two inputs from the user, based on those parameters creating a scatter quadrant plot and changing quadrants with respective user input parameters.

1. Created two what-if parameters.

Sepal Length and Sepal Width.

2. Created a scatter plot with X-axis - Sepal Length and Y-Axis - Sepal Width.

Added Constant line X- axis with what-if parameter value Sepal Length'[Sepal Length Value] and Constant line Y-axis with what-if parameter value 'Sepal Width'[Sepal Width Value].

3. So when the user changes the values in the slicer(what if parameter) in the scatter plot two Constant lines changes accordingly.

4. So as a result based on user inputs we will get 4 dynamic quadrants.

5. For coloring each quadrant points I used the following code which woking fine.

Colors =

VAR X = SELECTEDVALUE(iris[sepal.length])

VAR Y = SELECTEDVALUE(iris[sepal.width])

VAR selected_X = 'Sepal Length'[Sepal Length Value]

VAR selected_Y = 'Sepal Width'[Sepal Width Value]

RETURN

SWITCH(

TRUE(),

X <= selected_X && Y <= selected_Y , "#48B847",

X <= selected_X && Y > selected_Y , "#FF5733",

X > selected_X && Y <= selected_Y , "#805B86",

X > selected_X && Y > selected_Y , "#000000",

"#33FF96"

)

I am able to create a scatter plot successfully.

Problem Statement :

I am Expecting Something like this, tabular information on the Quderent level.

Slicer: Mean/Median

	Sepal Length	Sepal Width	Petal Lenth	Petal Width
Quadrant 1
Quadrant 2
Quadrant 3
Quadrant 4

If the user select Mean in Slicer the table should show the Mean of each column Quadrant wise, based on the User Sepal Lenght and Sepal Width inputs.

Same for the Median too.

Note: table value should change according to the two what-if parameters.

Thank you in advance,

Karthik

Anonymous

Not applicable

3 years ago

Hi karthikmiriyala,

You can create a new table with two modes to use in formula change calculation mode:

SwitchMode = {"Mean","Median"}

Here are the dynamic measure formulas based on two what-if parameter tables, Quadrant group and mode slicer.

petalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

petalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

sepalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

sepalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

Regards,

Xiaoxin Sheng

sample.pbix37 KB

Forum Discussion

Creating a table with values changing respective with what if parameter.

Related content

Python Notebook does not respect pipeline input parameter

Parameter in summarize

Can i use a measure selected in a slicer (created with field parameters) to create a rank?

Create Company/Project Hierarchy and show Projects at their respective level

Table Created with Field Parameters missing rows