Forum Discussion
Post PartisanPie Chart Problems - Can't Add Values for Totals Using Last Date Only
Hi, julesdude ;
You could create a measure.
total = var _max=CALCULATE(MAX('TableA'[Date]),ALLEXCEPT(TableA,'TableA'[Country])) return CALCULATE(SUM('TableB'[Total Value]),FILTER(TableB,[Date]=_max))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Championjulesdude I have adapted v-yalanwu-msft solution. Can you try this as a measure. Just adjust column names to what they are in your data.
Measure =
VAR _1 =
CALCULATE (
MAX ( 'Table'[Last Updated] ) ,
ALLEXCEPT ( 'Table' , 'Table'[Country], 'Table'[Asset Reference] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Amount] ) ,
FILTER ( 'Table'[Last Updated] = _1 )
)
Post PartisanThanks so much TheoC
The only problem is that country needs to be called from the other table - Table A via the Asset Reference link. Will the above still work? I simplified the latest table so give an idea of what it should look like as one. But the country is looked up using the asset reference link:
- julesdude
I've attached a sample workbook here removing unnecessary data and providing only needed columns as examples above. Data has been replaced slightly. :
- TheoC
julesdude okay, thanks so much for sending that through. Unfortunately, due to your Table B only having Asset References and no country, you cannot achieve what you are wanting because there is no way for Power BI to determine which Asset Reference is associated with which country.
For example, Both Italy and Egypt have the same Asset Reference (e.g. AAA can be either Italy or Egypt). And, given you have linked Table A and Table B via a relationship at the Asset Reference level (which is NOT a unique key), Power BI does not know which Country to return because there is nothing distinct or unique to identify the Country that the Asset Reference belongs to.
I hope that makes sense?
Cheers mate.
Theo
- julesdude
Hi TheoC
Thanks for looking at it. It sort of makes sense. I was a bit lost when you said AAA can be either Italy or Egypt because TableA is listing distinct references and the country column provides the country membership they belong to.
So if you're saying that the DAX can't lookup this reference to begin summing assets for each country, how else can I do this?
I had a thought - what if I merged the two tables/queries in Power Query so that I create a new column in Table B called Country which lists the corresponding country based on the asset reference join of Table A to Table B. We would then get the following:This could be better to work with as now all held in the same table, but I'd still be uncertain how the DAX would operate and call requiring the logic above?
- TheoC
Hi julesdude
As long as you have the ability to identify which Asset Reference belongs to which Country, then you can do it. The issue in this instance, with the data that currently exists in Table A and Table B, it cannot be done.
When I was putting together a new measure for you which started with a LOOKUPVALUE as the first variable, it wasn't letting add 'Table A'[Country] as the third parameter being the Search Value. The reason for this is because there's no way for Power BI to determine which Asset Reference belongs to a specific Country with respect to the population of data that currently exists.
Unless there is other data that you have which has not been shared, there is no way in which the outcome can be achieved unfortunately (from my understanding).
I hope this makes sense.
Theo