DAX Parameter Selected Value

[Anonymous]

Hi ,

My requirement is dynamically switch between different demographic categories using the slicer, display their proportions over time on the line chart, and add a reference line to show the gap for the selected category. I would like to see two lines one for young and one for muture with year on x axis, parameter on Legend and Proportion on Y axis.  I have created the table parameter for all the demographics categories lets say for example: 

Demographics =

DATATABLE(

    "Category", STRING,

    "Order", INTEGER,

    {

        {"Gender", 0},

        {"Age", 1},

        }   

YoungProportion =

CALCULATE(

    AVERAGE('table1'[proportion]),

    'table1'[Age] = "Young"

)

 

MatureProportion =

CALCULATE(

    AVERAGE('table1'[proportion]),

    'table1'[Age] = "Mature"

)

 AgeGap:YoungProportion-MatureProportion //This calculation is working OK for GAP calculation.

Here are is dax for selected category.

SelectedCategoryProportion =

VAR SelectedCategory = SELECTEDVALUE(Demographics[Category])

RETURN

    SWITCH(

        SelectedCategory,

        "Age",

            IF(

                HASONEVALUE('table1'[Age]),

                SWITCH(

                    VALUES('table1'[Age]),

                    "Young", [YoungProportion],

                    "Mature", [MatureProportion]

                )

            ),

        "Gender",

            IF(

                HASONEVALUE('table1'[Gender]),

                SWITCH(

                    VALUES('table1'[Gender]),

                    "Male", [MaleProportion],

                    "Female", [FemaleProportion]

                )

            ),

        BLANK()

    )                                                                                                                                                    If the age is selected , then it is showing blank for this selected category on the visuals . Could you please advise what is wrong in this above DAX calculation ? I am not getting any error message.

Many thanks for your help. Regards

[Anonymous · Accepted solution]

Hi @Anonymous 

I have tested your problem and your code seems to be fine. The key to the problem lies in the creation of visual objects.

I suggest you create two visual objects that show the data corresponding to different parameters.

Select "Gender"

Select "Age"

Select "line chart", "Year" in the x axis, "SelectedCategoryProportion" into the y axis.

It should be noted that "Gender" and "Age" are placed into the "Small multiples" of the two visual objects respectively.

                 

At the same time, you can adjust the visual object to two rows and one column.

Regards,

Nono Chen

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

[xifeng_L]

Can you provide some sample data or pbix file? The cause cannot be determined because the model and context are not clear.

[Anonymous]

Thanks for looking into this. Please below table for your rererence:

Age	Gender	Proportion	Year	Gap age	YoungProportion	MatureProportion	MaleProportion	FemaleProportion
Mature	Female	7.20%	2015/16	8.60%	0.157723577	0.071726893	0.080260304	0.071726893
Mature	Male	8.00%	2015/16	8.80%	0.168731563	0.080260304	0.080260304	0.071726893
Mature	Female	8.40%	2016/17	8.50%	0.16861167	0.083595922	0.07884097	0.083595922
Mature	Male	7.90%	2016/17	8.60%	0.165222415	0.07884097	0.07884097	0.083595922
Mature	Other	0.00%	2016/17	0.00%	0	0	0.07884097	0.083595922
Young	Female	15.80%	2015/16	8.60%	0.157723577	0.071726893	0.168731563	0.157723577
Young	Male	16.90%	2015/16	8.80%	0.168731563	0.080260304	0.168731563	0.157723577
Young	Female	16.90%	2016/17	8.50%	0.16861167	0.083595922	0.165222415	0.16861167
Young	Male	16.50%	2016/17	8.60%	0.165222415	0.07884097	0.165222415	0.16861167
Young	Other	0.00%	2016/17	0.00%	0	0	0.165222415	0.16861167

[Anonymous]

Thanks for looking into this. Please below table for your rererence:

Age	Gender	Proportion	Year	Gap age	YoungProportion	MatureProportion	MaleProportion	FemaleProportion
Mature	Female	7.20%	2015/16	8.60%	0.157723577	0.071726893	0.080260304	0.071726893
Mature	Male	8.00%	2015/16	8.80%	0.168731563	0.080260304	0.080260304	0.071726893
Mature	Female	8.40%	2016/17	8.50%	0.16861167	0.083595922	0.07884097	0.083595922
Mature	Male	7.90%	2016/17	8.60%	0.165222415	0.07884097	0.07884097	0.083595922
Mature	Other	0.00%	2016/17	0.00%	0	0	0.07884097	0.083595922
Young	Female	15.80%	2015/16	8.60%	0.157723577	0.071726893	0.168731563	0.157723577
Young	Male	16.90%	2015/16	8.80%	0.168731563	0.080260304	0.168731563	0.157723577
Young	Female	16.90%	2016/17	8.50%	0.16861167	0.083595922	0.165222415	0.16861167
Young	Male	16.50%	2016/17	8.60%	0.165222415	0.07884097	0.165222415	0.16861167
Young	Other	0.00%	2016/17	0.00%	0	0	0.165222415	0.16861167