Forum Discussion

Andvil's avatar
Andvil
Icon for Helper V rankHelper V
6 years ago
Solved

Visual 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 h...
Need Help
Show and Tell
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
Andvil
Icon for Helper V rankHelper V
6 years ago

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,

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Andvil where that 210 sales should get allocated? What is logic? 

    • Andvil's avatar
      Andvil
      Icon for Helper V rankHelper V
      6 years ago

      Hello parry2k,

       

      the 210 sales should be under the customer "01777794 Astet Agentes..". As seen on the picture, only 458 sales codes got assigned to an employee, but 210 sales codes were not assigned. 

       

      This picture does not have the "show items with no data" feature, under the employee with 458 sales code I should have another row, where the employee field is blank and in the sales column I have the 210 value (number of sales codes not assigned to any employee).

       

      The same thing with the next customer, under the rows with 392 and 7 sales codes assigned to employees, I should have another row with blank field in employees and the remaining sales codes that were not paired.

       

      Here is a file that might help: PBI File