Forum Discussion
Helper VVisual with incomplete information - Relationships
Hello community,
I created relationships between tables and I want to export the visual information as xls file. In this example, I assign "employees" to some "customers", but not all customers have an employe assigned. I realized that my visual only shows the sales that have been assigned and it does not consider the sales without an employee, and instead of showing it blank, it just does not include it.
When I do not enable the "show items with no data", my visual is like this one:
See that from the 668 sales, only 458 have an employee assigned. I need to see the other 210 sales even though they do not have an employee assigned.
When I enable the "show items with no data", my visual is this one:
See that it still shows 458 sales from the 668. By enabling the "show blank data", it shows the available employees but it still does not show the remaining 210 sales that are not assigned to an employee. And when I export to excel, it only exports the 458 sales, whereas I want all the sales where the 210 sales that are not assigned can have a blank in the employee field.
Is this possible?
Best,
Hi Andvil ,
Maybe you can use Measure instead of columns, like the following example.
Replace Item Column with Item Measure and put it in Values
Item Measure = var _x = CALCULATE(SUM('2020'[ITEMS])) return IF( ISBLANK(_x),"",_x)If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
8 Replies
- Greg_Deckler
Community Champion
Andvil - Maybe. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
AndvilHi Greg_Deckler
1) I attach an example file: PBI FILE
2) My output is to have under each employee and the sales codes, another row with a blank (as no employees are assigned) and the remaining sales codes that were not paired. There are more explanations in my previous messages.
As you can see in this picture, "01777794-Astet Agentes" has 668 purchases. 458 sales codes were assigned to an employee, however, 210 sales codes were not assigned and not shown in the table. Under the employee and the 458 sales, I should have a blank field in employee and then 210 under the 458, that way it matches the 668 total sales. I have enabled the "show items with no data" but still the information not assigned to an employee is not showing.
3) Power Bi must somehow show the information that has not been paired to an employee or agency, Imagine you have that column and not all the fields are filled with employee information, when adding the visual, you would have in blank all the sales that have not been assigned to an employee.
Best,
v-zhenbw-msftCommunity Support
Hi Andvil ,
Maybe you can use Measure instead of columns, like the following example.
Replace Item Column with Item Measure and put it in Values
Item Measure = var _x = CALCULATE(SUM('2020'[ITEMS])) return IF( ISBLANK(_x),"",_x)If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
- parry2k
Super User
Andvil you can choose show items with no value in your visual, for example for matrix visual, you can follow the screenshot below
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Andvil
Hi parry2k ,
I enabled the "show items with no data" option, it is the second picture of my post:
As you can see, the only difference from the first picture (that does not have the "show items with no data" option enabled) is that it shows all the available employees, but it does not show the 210 sales that have not been assigned to an employee.
So I already tried your solution, do you have any other suggestions?
Best,
- v-zhenbw-msft
Hi Andvil ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
