cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

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_IDDirectionAttemptColdWarm
1In1018129
2In11914790
3In1523770
4In6565116
5In102127144
6In153318
7In894533
8In10535119
9In3698115
10In906622

 

Table 2:

Call_IDDirectionAttemptColdWarm
1Out2212018
2Out11911788
3Out401639
4Out948290
5Out1107651
6Out13912449
7Out11359114
8Out1301359
9Out196955
10Out50738

 

I have a 'bridging' table for the call direction to join the tables.

 

tables.JPG

 

When creating a matrix, I get the columns being duplicated on each of the grouping category, and displaying blank values like below;

matrix with blanks.JPG

 

 

 

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.

PBI_File

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Hide blank values in matrix view

have a look at this file
https://1drv.ms/u/s!AjxUGXgGNzCEiV4mePewl33dzvil

Capture.PNG
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 10
Super User
Super User

Re: Hide blank values in matrix view

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

rax99 Member
Member

Re: Hide blank values in matrix view

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):

 

SD.JPG

 

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:

 

matrix2.JPG

If this can be achieved using a merged table then of course this will be a preffered solution.

Super User
Super User

Re: Hide blank values in matrix view

I'm not sure I get the complexity fully, but you could have a table like this one:
Capture.PNG

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

rax99 Member
Member

Re: Hide blank values in matrix view

@Stachu

 

Yes so I have a alternative SDTable that does this;

 

io.JPG

 

But the resulting matrix still ends up looking like this:

 

matrix2_new.JPG

How would I use a join SD table to filter the attempt_I, and attempt_O to their repective group, thus not showing any blanks?

Super User
Super User

Re: Hide blank values in matrix view

have a look at this file
https://1drv.ms/u/s!AjxUGXgGNzCEiV4mePewl33dzvil

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

Highlighted
rax99 Member
Member

Re: Hide blank values in matrix view

Thanks for that.

 

Unfortunatly it was the first stumbling block I hit. The measure is not doing what I intend from it.

 

 

Warm_AboveSD = SUMX(FILTER('Table','Table'[Warm]>=CALCULATE(SELECTEDVALUE(SDTable[Warm]))),'Table'[Warm])/60

What its doing: A sum on all the WARM values, regardless of them being Inbound or Outbound, and then comparing to the selected SD value (warm inbound or warm outbound).

 

What im after: The selected value should only be compared to the SUM of WARM_Inbound values, hence my original measures for each inbound and outbound seperatly.

 

Is there anyway around this? Thanks

Super User
Super User

Re: Hide blank values in matrix view

that's dpending on the visual filter context, and in all the screenshots you posted it was always unique to In/Out, never aggregated
I removed the division to make it more clear:
Capture.PNG

you can see that the code '>=' criteria only limited scope for the 'In', as that's what's filtered in the visual, even though the measure is the same for both

if you want this to be working on aggregated level as well then e.g. SUMX over SUMMARIZE could do the trick

rax99 Member
Member

Re: Hide blank values in matrix view

Sorry @Stachu I dont think I understand this correctly. 

 

so if we take your measure from your screenshot;

 

Warm_AboveSD = SUMX(FILTER('Table','Table'[Warm]>=CALCULATE(SELECTEDVALUE(SDTable[Warm]))),'Table'[Warm])/60

Its telling the data to get every warm record in 'Table' where it is more than selected value, which is SD = 1, and Warm In which is either 62 or 26. So are you saying the measure is only going to compare Table[Warm] values that are of category 'In' to selected SD value 62 (thus Table[Warm] Out to selected sd value of 26) as this is how the tables are joined/filtered?  

 

So in sql I would normally do something like this: 

 

SELECT SUM(Warm) FROM FactHold WHERE Direction = 'Inbound' AND Warm >= 62 -- which is SD1 value for warm inbound

 

(Sorry also the aggregation is done at the end after the SUMX correctly compares each row value. My fault for the confusion earlier)

rax99 Member
Member

Re: Hide blank values in matrix view

This does actually work, thank you very much @Stachu