DAX Workout 012 - Scatter Plot Highlighted Value

[Paul.Gerber]

Welcome to another DAX workout. In this workout, you are asked to create a scatter chart that will highlight the Country when selected by a slicer. Also, create a dynamic title that shows the country selected.

My colors are blue for the highlight and tangerine for the rest. Feel free to use your own colors.

If you want to challenge yourself further, modify the Dynamic Title measure to display multiple countries.

Use the COL DATA 2022 table. The other tables have extra information if you want to use it.

Submission

Load the supplied data file into a new Power BI file, create your solution, and reply to this post. Upload a screenshot of your solution along with the DAX measure. Please format your DAX code and blur it or place it in a hidden section.

Period
This workout will be released on Thursday, April 27, 2023, and will end on Sunday, April 30, 2023. But you can always come back to any of the workouts and solve them.

Global Cost Of Living Crisis - DAX 12.pbix (6.0 MB)

[AlexisOlson]

Is part of the workout intended to be deciphering the model? It’s not immediately clear to me what tables and dimensions are intended to be used and why there are so many.

[Paul.Gerber]

@AlexisOlson Use the COL DATA 2022 table. The other tables have extra information if you want to use it. This was part of a PBI Challenge.

Paul

[Paul.Gerber]

@AlexisOlson all you really need to look at is the country, GDP and the cost of living index and then build from there

[windler.nikki]

Thanx for the workout

[HufferD]

@Paul.Gerber , Can GDP be calculated from data in COL DATA 2022?

[Paul.Gerber]

Absolutely. I think the other dataset had different values. But I used the one you mentioned.

Thanks for the question. Enjoy the workout

[HufferD]

Ok. I didn’t see the column with GDP. I was mistakenly using your 'COL DATA 2022'[GDP_COLI stats test.Population] column at first. then I thought perhaps it was the 'GDP_COLI stats test'[GDP]. Maybe the column is hidden in there somewhere

At any rate, the GDP values below are incorrect but the minimal workout is complete:

Thank you very much for putting that together.

[sam.mckay]

I got this is maybe a unique way without really using any DAX

I think because of the model this actually worked quite well.

Don’t always have to use DAX if you can get the result in simpler ways.

[Paul.Gerber]

@HufferD great job. Glad you enjoyed the workout. What are your thoughts about the workouts so far?

Paul

[Paul.Gerber]

@sam.mckay absolutely. I never even thought of rules for it. But this gives me an idea for something else.

Thanks for doing the workout!!

Paul

[HufferD]

I’m really enjoying these workouts, honestly. They compactly present nuanced, real-life scenarios that you just can’t experience in such a short time otherwise. Some of the problems have been complicated enough that they’ve entertained me in my off time. And, so far, I’ve gained a nugget or two out of every one that I’ve approached.

I only follow the DAX, deneb, and data visualization workouts. And I joined the party late: I didn’t know about the workouts until a couple of weeks ago. I haven’t started to work backwards through the earlier problems.

Thanks again for planning these.

[Paul.Gerber]

Thanks for the great response @HufferD I appreciate that and knowing what you are learning or getting from these. They will always be available for you to come back to.

Have a great weekend.

Paul

[Eze]

@Paul.Gerber just double checking, the only GDP columns I can see in the COL DATA 2022 table are GDP_COLI stats test.Residuals & GDP_COLI stats test.Population.

I initially thought of doing a lookup on the “GDP_COL1 stats test” table using
country and cost of living index columns to fetch the GDP.

Can you please clarify which one to use, thanks

[Paul.Gerber]

@Eze hello. I used the COL_Data 2022 dataset.

[BrianJ]

@Paul.Gerber ,

I’m really liking the new Subtitle option for these sorts of dynamic titles:

DAX Measure for Conditionally Formatting Dot Color

CF Scatterplot = 

VAR __SelCountries = 
ALLSELECTED( 'Disconn Country'[Country] )

VAR __Result = 
IF( DISTINCT( 'gdp-per-capita-worldbank'[Country]  ) IN __SelCountries, "Blue", "Orange" )

RETURN __Result

DAX Measure for Dynamic Subtitle

Dynamic Subtitle = 

VAR __AllCountries = 
COUNTROWS( ALL( 'Disconn Country'[Country] ))

VAR __AllSelectedCountries =
COUNTROWS( ALLSELECTED( 'Disconn Country'[Country] ))

VAR __Result = 
IF( __AllCountries = __AllSelectedCountries,
    "(All countries selected)",
    "(" &
    CONCATENATEX( 
        ALLSELECTED( 'Disconn Country'[Country] ),
        'Disconn Country'[Country],
        ", ",
        'Disconn Country'[Country],
        ASC
    ) & " selected)"
)

RETURN __Result

Global Cost Of Living Crisis - DAX 12 - Brian Julius Solution.pbix (6.0 MB)

Another fun workout - thanks!

• Brian

[Paul.Gerber]

@BrianJ I am happy to hear you enjoyed it. Great solution

Paul

[mbraun]

Great DAX Workout !

[donnclark]

Fun workout, and as always educational. Thanks Brian, for submitting your .pbix file.

Here’s my submission: Donn Clark

DAX12-Global COfLiving Crisis-DEC.pbix (6.0 MB)

[shahadathossen.shajj]

Really amazing workout learned all the functions from the course, and these workouts really helped me to learned how and where to use those functions in different aspects

DAX Measure

This text will be hidden

Matching With Country =
VAR _label =
    ALLSELECTED ( 'Country Slicer Table'[Country] )
RETURN
    SWITCH (
        TRUE (),
        VALUES ( 'GDP_COLI stats test'[Country] ) IN _label, "Orange",
        "Blue"
    )