Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
you can try
Partners Total =
CALCULATE (
[Total],
CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH )
)
you can try
Partners Total =
CALCULATE (
[Total],
CROSSFILTER ( Partners[ContractID], 'Policies'[ContractID], BOTH )
)
@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.
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?
@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.
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
@amitchandak
I forgot to tell that in thid measure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |