Table not showing the records in the table visual based on filter selection.

[Anonymous]

I have two tables. There are some record in table2 11,12,13,14,15,16,17. Which are not in table1.

I created a relationship like below and cross filter both directions.

Table2 1:M Table1

Selected the table2.key attribute in the filter.

First added the attributes from the table1 to table visual and then added the attributes from the table2.

Selected a values “ 17”  from the filter which is not available in Table1.

 Table visual not showing the any data  

First added the attributes from the table2 to table visual and then added the attributes from the table1.

Selected a values “ 17”  from the filter which is not available in Table1.

Showing the data

what is the problem here , is this issue related to data or data in the model ? visual backend DAX calculation ?

[v-xicai · Accepted solution]

Hi @Anonymous ,

Even though there is no filters in Slicer visual, the result page values is different according to the order of displaying table fields.  See the similar case Order of Field Selection Changes the Data,  we know this behavior is by design. About the order of the fields matter, the documentation has an article explaining why the result is different depending on the order, see: https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data .

You may create an intermediate table like DAX below, then create relationships with your original two tables.

NewTable = UNION(DISTINCT(Table1[Table1.Key]), DISTINCT(Table1[Table2.Key]))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

better

Create a common dimension and have keys from both tables and use it

Keys = distinct(union(all(Table1[Key]),all(Table2[Key])))

I doubt most of the joins are right join 1 to M .