Personal

A colleague of mine came to me with an interesting use case, “ Switch between a summarized value or selected value with multiple legends”. For example, I have five countries and their GDP values. When the end user goes to the report, the user would like to see the average GDP of all countries, but when the user selects single or multiple countries on the slicers, the line chart should show only selected values. Like below:

There are multiple ways to achieve this; some are more elegant than others.

Layering visuals

First, I create two DAX measures to show values based on selection.

All avg =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
Return
if(
v1=V2,CALCULATE(AVERAGE('owid-energy-data'[Value]),ALL('Table'[Country])))

selected avg =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
Return
if(
v1=V2
,BLANK()
,AVERAGE('owid-energy-data'[Value])
)

Then I create two line charts, using the year on the X-axis and one of these measures on the Y-axis. Enable Legend on Visual, where I am using the “selected avg” measure.

Create two other measures for Titles:

Title All =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
Return
if(
v1=V2,"Average of all countries")

Title Selected =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
Return
if(
v1<>V2,"showing selected average")

Update respective visuals titles with conditional formatting

Make a few more changes, like disabling Axis titles and making more line charts of the same size. Here I have not updated Min and max axis, but sometimes that needs to be addressed based on data.

Then I get below viz. It nicely shows, Average value by default, and when the user chooses a country, it shows the selected country value.

One main issue with this approach is I can only see tooltips of the top visual. The same goes for all other “More options” of the visual.

Using a single measure

Another approach is to, instead of layering, use one measure to switch between average and selected values.

I create a new measure

Dynamic Avg =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
Return
if(
v1=V2,[All avg],[selected avg])

Also, create a new Title measure

Title =
VAR V1 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALLSELECTED('Table'[Country]))
VAR V2 = CALCULATE(COUNTROWS(VALUES('Table'[Country])),ALL('Table'[Country]))
VAR SelctedCountries = CONCATENATEX(VALUES('Table'[Country]),[Country],",")
Return
if(
v1=V2,"Average of all countries", IF(
COUNTROWS(values('Table'[Country]))=1, "GDP value of "& SelctedCountries&" ",
"GDP values of countries "& SelctedCountries&" "))

This enables the end user to view the average as the default and select value based on slicer selection.

But for User Experience, we need a legend to show what slicer has been selected, and things change as soon as we enable legend. Legend is enabled even when the average is displayed, and it causes confusion for the end user.

Instead of enabling legend, we may enable axis titles, but again for average, it automatically chooses a country based on sort order. This approach is good as long as you don’t want a legend.

Alternative to legend

The following approach is to find an alternative to legend. Instead of showing a legend, I make a copy of the visual, convert it into a tree map, and use it as a legend. Same logic as I blogged here – https://prathy.com/2022/06/using-tree-map-as-legend-on-a-page-in-power-bi/

Make a few more changes. Instead of using the Title on the Line chart, add a text box and show the Title. Group all three visuals and backgrounds to give the illusion of a single visual.

When a user lands on this page, it shows the average GDP value by default. When a value is selected in the slicer, it shows only that country. For usability, I have enough signifiers like Title and legend colour.

I hope this inspires someone out there. Until next time
Prathy 🙂

If you are a Power BI user group organiser, then it’s kind of obligatory to create a Power BI dashboard on Meet up data. Like many, I thought I would create a Power BI template for London PUG Power BI Meet Up (That’s another blog post!). However, pulling data from API services is never fun. When I started importing data from Meet Up API, at first it was seamless; quite quickly I pulled members data from London PUG Meetup site. Not far after that, I realised, I only had 200 members. I increased Page value to 1500, but still, still, I could see only 200 rows. After a bit of searching, I found this forum – https://stackoverflow.com/questions/35121504/get-more-than-200-group-members-from-the-meetup-api  .

Based on this, I could get only 200 results.  So I need to find a way to call the API with a dynamic offset value using Power Query. The total number of Offset value depends upon the page size and number of members. So first I need to find the total number of members in London PUG Meetup Group then calculate how many offsets I needed. Like any other language, there are many ways I could do this but I decided to go for List.generate() function.

Dynamically update Offset value for API calls using Power BI:

I read so many nice things about M function List.Generate(), but I don’t think I have ever used it properly. There are some really nice blog posts explaining list.Generate() function functionality. These are my favourite: PowerPivotPro, DataChant, Excel Inside and Excelando. If you want to understand the function, these blog posts are a good place to start. What I am showing in this blog post is to show how we can use this with API. Very similar to what they mentioned in their blog posts just a different data source, but I kinda have to write this post so I can remember for my reference.

Here is the M code I used :

let
    URL = "https://api.meetup.com/"&MeetupID&"/members?&sign=true&photo-host=public&page=200&offset=",

    //Get total number of memebers
    TotalNumber = let
                    Source = Json.Document(Web.Contents("https://api.meetup.com/2/members?&sign=true&photo-host=public&group_urlname=London-PUG&key=" & APIKey)),
                    #"meta" = Source[meta],
                    #"Converted to Table" = Record.ToTable(#"meta"),
                    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "total_count")),
                    Value = #"Filtered Rows"{0}[Value]
                in
                Value,

    //Find number of calls and Round up
    APICalls = Number.RoundUp(TotalNumber /200),

    //List.Generate - To create the dynamic URL 
    LG=List.Select(List.Generate(
                        ()=>[i=-1,newURL = null], 
                        each [i] <= APICalls,
                        each [i=[i]+1,newURL=URL & Number.ToText(i)&"&Key="&APIKey],
                        each try CallAPIURL([newURL]) otherwise null
            ), each _ <> null),

    
    #"Converted to Table" = Table.FromList(LG, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "name", "status", "joined", "city", "country", "localized_country_name", "state", "lat", "lon", "photo", "group_profile", "bio"}, {"id", "name", "status", "joined", "city", "country", "localized_country_name", "state", "lat", "lon", "photo", "group_profile", "bio"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"joined", Int64.Type}}),

    //Added custom column to update UNIX date time to normal date format
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateJoined", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0,[joined]/1000)),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"joined"}),
    #"Expanded photo" = Table.ExpandRecordColumn(#"Removed Columns", "photo", {"id", "highres_link", "photo_link", "thumb_link", "type", "base_url"}, {"photo.id", "photo.highres_link", "photo.photo_link", "photo.thumb_link", "photo.type", "photo.base_url"}),
    #"Expanded group_profile" = Table.ExpandRecordColumn(#"Expanded photo", "group_profile", {"role", "intro"}, {"group_profile.role", "group_profile.intro"})
in
    #"Expanded group_profile"

I created two Parameters, one for MeetupID and another one for API Key. Then I kind of used a subquery to find TotalMembers. I divided the Total number with 200 to calculate the number of API calls I needed. After that List.Generate() handles all the hard work.

It’s not only Meetup API, there are many API services which use filters like pages and offset, and List.Generate() works pretty well.

Prathy 🙂