How to display actual values instead of null values

[Anonymous]

Hi, In a table chart i see few rows with null values but they have values in table b. join between two tables is M*-M* in both direction. but i see totals even though rows having null values. 

Tran ID	DATE	qty_col
234	6/17/2019 0:00
2344	6/17/2019 0:00
4567	6/17/2019 0:00
123	6/17/2019 0:00
111	6/17/2019 0:00
234	6/17/2019 0:00
456	6/17/2019 0:00
2345	6/17/2019 0:00
Total	l	1386

Displaying as above mentioned please help me here why i am not able to see values but able to view total.

[v-xicai]

Hi   @Anonymous ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous · Accepted solution]

Hi Amy,

Thanks for your response.  We tried all the steps suggested but it did not work for us.  Finally we digged more in data side and found in Tabe B we see null record which is causing M-M relationship. We hided the null record and finally issue resolved.

Thanks,

Vijay 

[v-xicai]

Hi @Anonymous ,

>>3. Created relationships between bridge table and other tables (one-to-many) like     (Table1) *<---1 (Bridge) 1-->* (Table2)

For this step , you may also change the Cross filter direction from Single to Both, , which will take these tables treated as a single table.

In addition, you may try to turn on the option "Don't Summarize" and  "Show items with no data" beside [qty_col] or [Tran ID].

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.

[Greg_Deckler]

@Anonymous - Avoid direct Many-to-Many relationships if at all possible. Create a bridge table between the two tables like this:

Bridge Table = 
  DISTINCT
    UNION(
      SELECTCOLUMNS('Table1',"TranID",[TranID]),
      SELECTCOLUMNS('Table2',"TranID",[TranID])
    )
  )

[Anonymous]

Thanks for the response @Greg_Deckler 

We tried the same you suggested earlier by creating new bridge table between 2 tables. we appened the columns from both the tables and removed nulls,blanks&error.

It is taking longer time to run,as it has 1.2M unique records. Failing to load data into visual, getting error :  out of memory.

[Greg_Deckler]

@Anonymous - Are you getting the error trying to create the table or in the visual? Tell me where it is breaking down:

1. Create bridge table in model

2. Remove relationship between tables (many-to-many)

3. Create relationships between bridge table and other tables (one-to-many) like     (Table1) *<---1 (Bridge) 1-->* (Table2)

4. Replace key column in visual with the one from the Bridge table

[Anonymous]

Hi @Greg_Deckler  

thanks for response we followed all the below steps

1. Created bridge table in model

2. Removed relationship between tables (many-to-many)

3. Created relationships between bridge table and other tables (one-to-many) like     (Table1) *<---1 (Bridge) 1-->* (Table2)

4. Replaced key column in visual with the one from the Bridge table

We are getting values and the totals when we add key column in the visual from bridge table.

when we add a column from (Table2) we see null values in the rows, but able to see/view the totals for those rows.  

our requirement is to add that column into the visual.

[Greg_Deckler]

@Anonymous - Any chance you can share PBIX?