Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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.
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
Yes so I have a alternative SDTable that does this;
But the resulting matrix still ends up looking like this:
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?
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
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
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:
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
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)
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
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |