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

Card not updating when clicking on chart using a calendar table

Grateful for any help you can give.

 

I have a table of orders here connected to a calendar table with 2 relationships, one from the Ordered On date, one from the Shipped On date:

 

 

Here is what the orders look like:

 

 

I want to make a card representing the total number of orders. For example: if I have a bar chart or orders by month, and I click a column in that bar chart, I want the card to update showing the total orders in that column. This works correctly if I use the calculated columns Ordered On Month and Shipped On Month:

 

 

But I don't want to create lots of calculated columns for all my tables across the data model. So I created a calendar table with multiple relationships and then used USERELATIONSHIP and measures which I thought would solve the issue. However, when I click on a column in the bar chart, it updates the Count of Orders card with the incorrect number, like it is still reading the month from the main relationship.

 

 

So my question is: how to create a calendar table and measures in such a way that cards update correctly with the number specified in the column clicked, not in the respective column of the active relationship.

 

I've linked the workbook here and happy to answer questions.

 

https://drive.google.com/open?id=1qJARtDN0KekQd1uSOWg5IqM71oIs9tA0

3 REPLIES 3
Super User
Super User

Re: Card not updating when clicking on chart using a calendar table

That's not going to work because one of your relationships is not active. But, if I understand what you are trying to accomplish, I have a couple quick measure gallery submissions that may help:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ALester Frequent Visitor
Frequent Visitor

Re: Card not updating when clicking on chart using a calendar table

Thanks Greg, yes that's clear.

 

Your links are useful because I will need to calculate the number of orders in progress per unit of time (at some point in the future). However that's not the problem I'm experiencing here. Here, I am merely concerned with 2 separate KPIs: the number of orders created, and the number of orders shipped, and the interaction between these 2 KPIs.

 

To put it as succinctly as possible: I want to have the behaviour of the 3rd screenshot*, without the need to create an arbitrarily large amount of calculated columns across the data model containing various formattings of dates. I want to do it in a more Effective, Idiomatic way (e.g. a calendar table).

 

* I define correct as: on clicking a column on either chart, the card shows the number of orders created (or shipped) in that column. The 2nd chart, showing shipped (or created) orders, is highlighted according to the orders selected in the 1st chart.

Community Support Team
Community Support Team

Re: Card not updating when clicking on chart using a calendar table

@ALester,

 

Two Cards are needed just like different measures dragged to the charts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 132 members 2,025 guests
Please welcome our newest community members: