cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

How to display actual values instead of null values

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 IDDATEqty_col
2346/17/2019 0:00 
23446/17/2019 0:00 
45676/17/2019 0:00 
1236/17/2019 0:00 
1116/17/2019 0:00 
2346/17/2019 0:00 
4566/17/2019 0:00 
23456/17/2019 0:00 
Totall1386

 

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

1 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 

View solution in original post

8 REPLIES 8
Community Support
Community Support

Hi   @vkrvkr1 ,

 

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

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 

View solution in original post

Community Support
Community Support

Hi @vkrvkr1 ,

 

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

Super User IV
Super User IV

@vkrvkr1 - 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])
    )
  )

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

@vkrvkr1 - 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

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

 

@vkrvkr1 - Any chance you can share PBIX?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors