Reply
avatar user
Anonymous
Not applicable

DAX Parameter Selected Value

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
1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

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"

 

vnuocmsft_0-1718333546669.png

Select "Age"

 

vnuocmsft_1-1718333569943.png

 

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.

 

vnuocmsft_3-1718334218425.png                 vnuocmsft_5-1718334329832.png

 

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

 

vnuocmsft_6-1718334400252.png

 

Regards,

Nono Chen

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

 

 

 

 

 

 

 

 

 

 

 

View solution in original post

4 REPLIES 4
v-nuoc-msft
Community Support
Community Support

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"

 

vnuocmsft_0-1718333546669.png

Select "Age"

 

vnuocmsft_1-1718333569943.png

 

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.

 

vnuocmsft_3-1718334218425.png                 vnuocmsft_5-1718334329832.png

 

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

 

vnuocmsft_6-1718334400252.png

 

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
Super User
Super User

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

avatar user
Anonymous
Not applicable

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

 

 

Age

GenderProportionYearGap ageYoungProportionMatureProportionMaleProportionFemaleProportion
MatureFemale7.20%2015/168.60%0.1577235770.0717268930.0802603040.071726893
MatureMale8.00%2015/168.80%0.1687315630.0802603040.0802603040.071726893
MatureFemale8.40%2016/178.50%0.168611670.0835959220.078840970.083595922
MatureMale7.90%2016/178.60%0.1652224150.078840970.078840970.083595922
MatureOther0.00%2016/170.00%000.078840970.083595922
YoungFemale15.80%2015/168.60%0.1577235770.0717268930.1687315630.157723577
YoungMale16.90%2015/168.80%0.1687315630.0802603040.1687315630.157723577
YoungFemale16.90%2016/178.50%0.168611670.0835959220.1652224150.16861167
YoungMale16.50%2016/178.60%0.1652224150.078840970.1652224150.16861167
YoungOther0.00%2016/170.00%000.1652224150.16861167
avatar user
Anonymous
Not applicable

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

 

 

Age

GenderProportionYearGap ageYoungProportionMatureProportionMaleProportionFemaleProportion
MatureFemale7.20%2015/168.60%0.1577235770.0717268930.0802603040.071726893
MatureMale8.00%2015/168.80%0.1687315630.0802603040.0802603040.071726893
MatureFemale8.40%2016/178.50%0.168611670.0835959220.078840970.083595922
MatureMale7.90%2016/178.60%0.1652224150.078840970.078840970.083595922
MatureOther0.00%2016/170.00%000.078840970.083595922
YoungFemale15.80%2015/168.60%0.1577235770.0717268930.1687315630.157723577
YoungMale16.90%2015/168.80%0.1687315630.0802603040.1687315630.157723577
YoungFemale16.90%2016/178.50%0.168611670.0835959220.1652224150.16861167
YoungMale16.50%2016/178.60%0.1652224150.078840970.1652224150.16861167
YoungOther0.00%2016/170.00%000.1652224150.16861167
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)