Forum Discussion
Post PartisanCalculate through a related table
you can try
Partners Total = CALCULATE ( [Total], CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH ) )
Post PartisanThat is exactly my point amitchandak
I wouldn't need any filter.
This table is connected to another table (which doesn't matter because i don't have any measure or column from it) but it's also connected with Dates table with an inactive connection. This also shouldn't matter...
Can you come up with any other measure solution?
Super UserpedroccamaraDBI , Assume you are using table Fact and the fall in filter direction of Dim1 , Dim2 via dim 3, then all these dim should filter fact.
If there is inactive join and you can not active that using userelationship
you can push that to filter
//Date1 is independent Date table or inactive join
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = MINX(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_min && 'Table'[Date] <=_max))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- pedroccamaraDBI
amitchandak
I forgot to tell that in thid measureCALCULATE([Total],RELATEDTABLE( Partners))
the [Total] is a calculated sum with userelationship of dates.
Also, my report has none dates filter or values. It's a report based on the sum of everything.
And i don't need to stablish any relationship....
Kinda weird solution, no?
About those measures above, don't you think the 2nd is faster than the first and then yours?
Also, i've done a new matrix visual, with any measure, from simple to less simple measure and the result is the same. - pedroccamaraDBI
Another thing amitchandak
My model as the image above: I have this policies table, one line per policy number. It is a unique table by policies. This table is connected with Dates with an inactive relation between dates. The policies table is connected with partners table, one to many, because you can have several type of partners for each policy number. Types of partner? Seller, manager, mediator, etc.
My visual doesn't have any date nor as a filter. My report has a filter that allow us to choose the type of partner, but is always sellected to seller. You're suppose to be able to change it.
My main measure above, Total, is a simple calculate with sum of total column with userelationship between dates.
Now the measure that i need, all 3 above works fine but they're taking to long to calculate and i believe the reason why my pbi file is almost 30MB.
The main question: what this measure would have to be like in order to be faster?
Thank you very much