Forum Discussion
Hide blank values in matrix view
I have 2 tables containg call_IDs with a specific call direction (so table1 contains incoming, table 2 outgoing)
Table1:
| Call_ID | Direction | Attempt | Cold | Warm |
| 1 | In | 101 | 81 | 29 |
| 2 | In | 119 | 147 | 90 |
| 3 | In | 152 | 37 | 70 |
| 4 | In | 65 | 65 | 116 |
| 5 | In | 102 | 127 | 144 |
| 6 | In | 15 | 33 | 18 |
| 7 | In | 89 | 45 | 33 |
| 8 | In | 105 | 35 | 119 |
| 9 | In | 36 | 98 | 115 |
| 10 | In | 90 | 66 | 22 |
Table 2:
| Call_ID | Direction | Attempt | Cold | Warm |
| 1 | Out | 22 | 120 | 18 |
| 2 | Out | 119 | 117 | 88 |
| 3 | Out | 40 | 16 | 39 |
| 4 | Out | 94 | 82 | 90 |
| 5 | Out | 110 | 76 | 51 |
| 6 | Out | 139 | 124 | 49 |
| 7 | Out | 113 | 59 | 114 |
| 8 | Out | 130 | 13 | 59 |
| 9 | Out | 19 | 69 | 55 |
| 10 | Out | 50 | 7 | 38 |
I have a 'bridging' table for the call direction to join the tables.
When creating a matrix, I get the columns being duplicated on each of the grouping category, and displaying blank values like below;
Clearly these blanks highlighted above should not be appearing. How do I get rid of these?
Note. This is a simplified version of a more complex table/data structure with more calculations etc so unfortunatly I cannot combine the In/Out tables (which does fix the issue in fact)
See attached pbi file for full example.
have a look at this file
https://1drv.ms/u/s!AjxUGXgGNzCEiV4mePewl33dzvil
with the way joins are set up you don't even have to specify in/out filter cause it comes from the visual fitler context
10 Replies
- Stachu
Community Champion
why not appending the two tables? they seem to have indentical structure, you already have in/out flag and you will not need the bridge anymore + the matrix should show it as one
Althoughyou are correct in this example as it doesnt make sense to split the table, however the actual production report contains indivdual measures which splits the attempt/cold/warm states based on their standard deviation values.
So I have a SDTable where it contains the measures that calculates the SD 1 and 2 for each type of call (eg Warm_In, Warm_out, Attempt_In, Attempt_Out, etc):
Then I have filter where I can select the SD value from the SDTable above.
See below I still get the same issue when using a merged table:
If this can be achieved using a merged table then of course this will be a preffered solution.
- Stachu
I'm not sure I get the complexity fully, but you could have a table like this one:
and use this code for SELECTEDVALUE to specify between In/Out
CALCULATE(SELECTEDVALUE(SDTable[Attempt]),SDTable[Direction]="In")
or you could do the Join table with SD with In/Out values only so it would always be filtered for single entry, but that may make model more complex