Forum Discussion
blank values with relationship
Hello All,
I have a dataset with 3 tables:
Calls (containing calls info and phone number)
Line_num ( phone number, site_id)
Site (site_id, site address)
and all the relationships working fine, however when I try to count the number of calls grouped by site address I have always a blank row counting the number of calls that are not associated to a site, I want to change this blank row to show "not available")
- Anonymous That solution will work, only potential issue is that Joins (or merge in Power Query language) can drastically slow down the refresh time of your query when compared to using the relationships. This usually isn't a problem though, especially if you can set an automatic refresh to load overnight.
Great troubleshooting and glad I could inspire a solution for you! Please mark this post as solved so others can easily find the answer.
Cheers!
Allison
16 Replies
nvprasadSolution Sage
Hi,
Assuming you are using measure for calcualting count and you can use if condition to get the disired output.
Ex: IF( [CALL COUNT MEASURE] = BLANK(), "Not Available",[CALL COUNT MEASURE])
Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂Regards,
N V Durga Prasad- DebbieE
Community Champion
Hi Anonymous
You could try something like this possibly with DAX
Sales (No Blank) = IF( ISBLANK([Sales]), "not available", [Sales] )
I dont think its recommended though
https://docs.microsoft.com/en-us/power-bi/guidance/dax-avoid-converting-blank
- amitchandak
Super User
Anonymous , this is coming because of right join. means some site value is missing in the master table
otherwise create a new column like coalesce(Table[Site],"NA")
In table put Key from Master Table and Fact table (1-M). And check for null on one side and find the missing
I have tired your solution and got the same blank row.
amitchandak wrote:In table put Key from Master Table and Fact table (1-M). And check for null on one side and find the missing
can you please elaborate more?
- AllisonKennedyAnonymous
What column is used to relate the call table to the line table? Please add this to the visual from the call table.
What column is used to relate the line table to the site table? Please also add this to the visual from the line table.
These are where your blanks most likely come from - the fact that those values are either null or do not exist in the line and/or site tables. The proper way to fix this would be to add a record to the line and/or site table for these values. If you don't know the site, you can add a line to the site table with an ID column that is new/unique (such as NA001) and a Site name of Unavailable. Then in your Power Query, you can do a replace values for any null in the LineID column of the Call table to find null and replace with NA001 so that the site name 'Unavailable' is pulled through to the report rather than blank.thanks for the reply,
actually the Line and site table does not have empty or blank rows, the difference coming from that main table which is call table does have more records than the line/site table which is ok with me but for those not matching values I want to show them as not available.
here is how the relationship works:
from Call table to line to site:
1- call.number =line.number
2.line.site_id=site.Site_Id
I've created those relations using the relationship tab not joining any tables from query editor.
- DebbieE
thats what I meant in the post. Apologies
So
Site ID Site Name
1 site a
2 site b
3 site c
4 site d
call site ID
10300
24 1
235 2
35 3
35 4
this is currently how its working because there is no records in Site against the call
so if you actually create that join like this
Site ID Site Name
1 site a
2 site b
3 site c
4 site d
5 No site registered
call site ID
10300 5
24 1
235 2
35 3
35 4
then you will always have a record against the call table which groups down to No site registered or what ever else you want to call it
This is asuming that Site is a list of sites, not duplicated.
