Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rax99
Helper V
Helper V

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
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

10 REPLIES 10
Stachu
Community Champion
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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

@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?

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

This does actually work, thank you very much @Stachu

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

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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)

Stachu
Community Champion
Community Champion

the measure is always evaluated in the specific filter context that is coming from visuals - see more detailed explanation here
https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

 

The filter context coming from the visual is
for the left part of the table: Direction[Direction]= "In"
for the right part of the  table: Direction[Direction]= "Out" 

The filter context from Direction table propagates to both SDTable and the fact Table, which then gives different numbers




Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.