topic Re: Calculate through a related table in DAX Commands and Tips

Calculate through a related table

pedroccamaraDBI — Wed, 16 Mar 2022 09:54:14 GMT

Hi guys,
I hope everyone's ok.
I'm building a matrix visual that shows me by for each type of policy, the total by 4 levels, Company, cost center, product and partner.
All these columns are in the same table, policies, except the partner which is in partners table, connected actively with policies.
I want to calculate the sum of values for each level and i came up with these measures:

CALCULATE(

[Total], VALUES( Partners),

TREATAS( VALUES( Partners[ContractID]),'Policies'[ContractID] ))
which works but i believe it's taking to long to calculate and sometimes gives me this error, and then it goes away.

Then i came up with another measure, which also is taking to long to calculate:

CALCULATE([Total],

RELATEDTABLE( Partners))

Can you help me on what is the best measure for this one?
Thank you all very much

Re: Calculate through a related table

amitchandak — Wed, 16 Mar 2022 09:58:59 GMT

pedroccamaraDBI , if policies table is connected with a partner then why the need of these filters

you can check for non blank

CALCULATE([Total],
filter(Partners, not(isblank(Partners[ContractID] )))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Calculate through a related table

pedroccamaraDBI — Wed, 16 Mar 2022 10:54:56 GMT

That 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?

Re: Calculate through a related table

amitchandak — Wed, 16 Mar 2022 10:59:07 GMT

pedroccamaraDBI , 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.

Re: Calculate through a related table

pedroccamaraDBI — Wed, 16 Mar 2022 12:30:45 GMT

amitchandak
I forgot to tell that in thid measure

CALCULATE([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.

Re: Calculate through a related table

pedroccamaraDBI — Wed, 16 Mar 2022 13:21:14 GMT

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

Re: Calculate through a related table

tamerj1 — Wed, 16 Mar 2022 14:24:09 GMT

Hi pedroccamaraDBI

you can try

Partners Total = CALCULATE ( [Total], CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH ) )

Re: Calculate through a related table

pedroccamaraDBI — Wed, 16 Mar 2022 15:52:24 GMT

Hi tamerj1
Great idea and many thanks for your help.
Best regards