cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rees
Frequent Visitor

Stacked Column Chart Linked to Card

Hello, I am pretty new to PowerBI. I have a dashboard (not created by me) that has a stacked column chart showing total user count per month for the past 13 months. There is also a card showing the total user count for one particular user type (the stacked chart includes all user types).

 

When I click on a single column in the stacked chart, the card shows the counts for that user type. This is good.

When I click off of specific column (so that the entire stacked chart is 'focused', the card shows counts for that user type,  across the entire 13 month span. This is good. 

 

So, there is a linkage there.  I would like to make the title dynamic. So, across all 13 months shown (Aug/2018 to Aug/2019 for example), I would like to make the title "9/2018-9/2019 Type A UCount" for the Card. If I click on 4/2019, I would like the card title  to be "4/2019 Type A UCount".

 

I have successfully made dynamic titles using DAX expresssions (I think that is what they are called). I have a successfull DAX expression changing the title to the specific month/year that is clicked on (in the stacked chart). However, I cannot seem to determine when all columns are focused. 

I don't know how to do it based on the specific bar clicked on in the Stacked Column Chart.  Is there a way? Seems like there should be. I mean, I've used various reporting tools for quite some time, and dynamic column labels are pretty easy to do.

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Rees ,

Actually, the highlight effect is similar to normal filter, you can use dax functions(allselected/values, min/max) to get current selection.

Sample title measure:

title measure =
IF (
    COUNTROWS ( VALUES ( 'Table'[Date] ) ) > 1,
    MIN ( 'Table'[Date] ) & " - "
        & MAX ( 'Table'[Date] ) & " sales",
    MIN ( 'Table'[Date] ) & " sales"
)

72.gif

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Rees
Frequent Visitor

I don't know if you were able to see my results on this suggestion. I can see in your example that it is working. Unfortunately, in mine it is not. I believe I recreated everything you did. I believe the cause is that the Return from COUNTROWS is always the total number of columns shown in the Stacked Column Chart. So, even when highlighting a single chart by clicking on it, the data changes, but the title stays the same because the result from COUNTROWS is always 13.

Do you have any idea why that might be?

Rees
Frequent Visitor

Hi, I must be doing something differently than you. Your example clearly shows this working. I have the following DAX expression defined:

MAUcardTitle = IF ( COUNTROWS ( VALUES ( 'MAU Rollups'[YearMonthDisplay] ) ) > 1, MIN ( 'MAU Rollups'[YearMonthDisplay] ) & " - " & MAX ( 'MAU Rollups'[YearMonthDisplay] ) , MIN ( 'MAU Rollups'[YearMonthDisplay] ) )
 
Also, if I add COUNTROWS ( VALUES ( 'MAU Rollups'[YearMonthDisplay] ) ) to the TRUE side if the IF statement, I see 13, always. Even when one column is selected. 
 
I am always seeing 2018-08 - 2019-08. Even when I click on a single month (any month in the stacked chart). 
The value displayed on the card does change appropriately as I click on and off of different columns in the stacked chart. It's just the title that I cannot yet change dynamically. It's strange because I can clearly see your example is working, and it is using COUNTROWS
DynamicCardTitleBasedOnStackedChartFocus.png
v-shex-msft
Community Support
Community Support

HI @Rees ,

I found your date values are stored in text type, some of DAX functions can't work properly with text values. (min/max works on numeric or date type)
Please modify your filed type to 'date', then my measure formula can extract corresponding values.

1.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Rees
Frequent Visitor

Unfortunately that solution does not work. I am simplifying the issue in order to help eliminate possible causes. 

The following DAX expression results in "A + B" all the time, even when one column is clicked on (focused/selected). 

IF ( COUNTROWS ( VALUES ( 'MAU Rollups'[YearMonthDisplay] ) ) > 1, "A + B" , "JUST ONE" )


This matches what I found earlier, that no matter if one column is selected, or not, COUNTROWS always returns 13 (the number of columns). In your example, unless I'm missing it, your COUNTROWS statement returns different values when a single column is clicked on

I think the problem may be the can the problem be the data model? It is a direct query, that always returns 13 records?

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors