Forum Discussion
Crossfilter direction of active relationshsips
Hello!
Sorry if it's dumb question, but i am really confused.
Here is a sample of ultra simple model (3 tables, 3 rows each)
Relationships are one to many with corect crossfilter direction.
As I undersand, in visual table field 'location'[name] SHOULD NOT be shown, as table 'Contract_location' DOES NOT filter table "Location". None measures used in this sample.
I quess that this behavior appeared recently.
Thanks!
I think this is probably not new behaviour but we haven't noticed before because it isn't usual to have columns in a table or matrix without associated measures.
The reason that your [count 1] measure returns 6 rows is the way SUMMARIZECOLUMNS works. It doesn't just use existing combinations of values, it does a crossjoin of all values from column 1 and all values from column 2, so it returns all possible combinations. It then executes the measure against each combination, and if the measure returns blank then it throws that combination away. If the measure doesn't return blank then it displays that combination.
Your [count 1] doesn't rely on the Contract table at all, so any value from Contract, when combined with a value from location, will return a non-blank value and therefore will be displayed.
The method that Power BI uses, doing a COUNTROWS of the table which both dimensions are related to, doesn't rely on cross filtering. It relies on the fact that both dimensions filter the table, so only valid combinations will result in a non-blank result.
5 Replies
johnt75Super User
Not a dumb question at all.
If you use Performance Analyzer to get the query generated for the table visual, and look at it in DAX Query View, or DAX Studio, you'll see something like
__DS0Core = SELECTCOLUMNS( KEEPFILTERS( FILTER( KEEPFILTERS( SUMMARIZECOLUMNS( 'abo'[abo_id], 'Location'[name], "CountRowsContract_Location", COUNTROWS('Contract Location') ) ), OR( NOT(ISBLANK('abo'[abo_id])), NOT(ISBLANK('Location'[name])) ) ) ), "'abo'[abo_id]", 'abo'[abo_id], "'Location'[name]", 'Location'[name] )In the SUMMARIZECOLUMNS it has added a measure to do a COUNTROWS over the Contract Location table so it only returns combinations of abo[abo_id] and Locations[loc] which exist in the Contract Location table.
I've never seen this behaviour before, but I don't ever remember looking at a query which didn't include a measure, so I can't say for sure whether the behaviour is new or not, but I doubt it. If you deactivate one of the relationships then the visual gives an error because it can't work out the relationship between 2 unrelated tables, and evidently doesn't want to show the cartesian product of both tables.
Thanks for your answer. But I still can not figure out - is it normal behavior for this visual (and has been for years and i just didn't noticed) or this is same kind of recent update.
Besides - if use measure likecount 1 = distinctcount(location[lt])and put it this visual table, then result is as expected wrong - 6 rows and lost relations.
But if crossfilter is added to measure:count 2 = calculate(DISTINCTCOUNT(location[lt]),crossfilter(location[lt], contract_location[lt], both))
then result is as expected correct - 3 correct rows
- johnt75
I think this is probably not new behaviour but we haven't noticed before because it isn't usual to have columns in a table or matrix without associated measures.
The reason that your [count 1] measure returns 6 rows is the way SUMMARIZECOLUMNS works. It doesn't just use existing combinations of values, it does a crossjoin of all values from column 1 and all values from column 2, so it returns all possible combinations. It then executes the measure against each combination, and if the measure returns blank then it throws that combination away. If the measure doesn't return blank then it displays that combination.
Your [count 1] doesn't rely on the Contract table at all, so any value from Contract, when combined with a value from location, will return a non-blank value and therefore will be displayed.
The method that Power BI uses, doing a COUNTROWS of the table which both dimensions are related to, doesn't rely on cross filtering. It relies on the fact that both dimensions filter the table, so only valid combinations will result in a non-blank result.
v-kpoloju-msftCommunity Support
Hi KasparsVV,
May i know has your issue been resolved? If the response provided by the super user johnt75, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.
Thank you for your understanding!
- v-kpoloju-msft
Hi KasparsVV,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
