Cross-highlighting based on measure

[Jero]

Hi,

I'm working on an error reporting project, which is based on the following data structure:

There are slicers for the department and the agent, and bar charts for the "category" and the "severity" of the error. Finally, there is also a funnel chart which combines the data of five boolean parameters to give an overview of the total impact of the error:

The visual interactions between these charts en slicers works perfectly fine, with one exception: Selecting one or more bars in the "Impact(s)" funnel chart does not, as I would like, affect the other two bar charts. It does work the other way around however: selecting one or more bars in these visuals does change the highlighting in the funnel chart.

I'm sure this can function the way I want, but have been unable to make it work. Can anyone help me out?

Thanks in advance!

Jero

[v-yulgu-msft · Accepted solution]

Hi @Jero,

In your scenario, click one bar in in "Impact(s)" funnel chart cannot filter the other two bar charts. This is by design. Because you added serveral columns ([NonRecuperableLoss], [RecuperableLoss], [PotentialFutureLoss], [Gain] and [PotentialFutureGain]) into funnel chart, when you clicked one bar in funnel chart, actually, you were making column level filters. This operation didn't filter out any record row in other two charts. That is why the other two charts are not filtered, they supports row level filters.

When you select [Gain] in funnel chart, all record rows are available.

When you select [High] in column chart, only highlighted rows are available.

An other example for your reference, if you create funnel chart like below.

When clicking a bar in this chart (e.g. True), it can affect other two charts.

Best regards,
Yuliana Gu

[Jero]

Sorry to double-post, but I still haven't been able to figure this out. I feel like this shouldn't be too complicated, but I can't wrap my head around DAX-logic just yet, it seems.

[Jero]

And the auxiliary table tblSortOrder:

SortOrder;severitySort
1;Low
2;Medium
3;High
4;Very high

[Jero]

PrimaryKey;Department;Agent;ErrorCategory;Severity;NonRecuperableLoss;RecuperableLoss;PotentialFutureLoss;Gain;PotentialFutureGain
4192080;New York;CHA D;1;Low;True;False;False;False;False
3077066;New York;ER-JA;4;Medium;False;False;False;False;False
4098658;New York;NE DE;5;Medium;True;False;False;False;False
2669534;New York;EE HE;6;High;True;False;False;False;False
3076206;New York;EE HE;4;Medium;False;False;True;False;False
4040014;New York;EE HE;7;Low;False;False;False;True;False
4383688;New York;ER-JA;4;Medium;False;False;False;True;False
2949382;New York;CHA D;2;Low;False;False;False;False;False
2280660;New York;ANIE ;1;High;False;False;False;False;False
3157908;New York;EE HE;2;High;False;False;False;False;False
2257732;New York;IEN D;2;Low;False;False;False;False;False
2951102;New York;E VAN;2;Low;False;False;False;False;False
2000398;New York;VERBE;1;Medium;False;False;False;False;False
3195760;New York;CHA D;6;High;False;False;True;False;False
2519728;New York;Y BAE;2;Low;False;False;False;False;False
4354454;Houston;ME VA;3;Very high;False;False;False;True;False
3073214;Houston;ID WA;5;High;False;False;True;False;False
4431914;Houston;EENTJ;3;Medium;False;False;False;False;False
3128654;Houston;T PYL;4;Very high;True;False;False;False;False
2753438;Houston;AN DE;3;Medium;False;False;True;False;False
4173580;Houston;RIE T;3;Medium;False;False;False;False;False
2111406;Houston;RIE T;5;High;False;False;False;False;False
4489506;Houston;NE MA;4;Low;False;False;False;False;False
4214694;Houston; VAN ;2;Low;False;False;False;False;False
3006820;Houston;E VAN;5;Medium;False;False;False;False;False
2718138;Houston;AN DE;5;Low;False;False;False;False;False
4425590;Houston;NS PE;2;Medium;False;False;False;False;False
1689456;Houston;E VAN;9;High;False;False;False;False;False
4199264;Houston;E BER;3;Medium;False;False;False;False;False
306004;Houston; GELS;3;Very high;True;False;False;False;False
2372018;Houston;NE MA;9;Low;False;False;False;False;False
114754;Houston;AN DE;2;Low;False;False;False;False;False
4184028;Houston;ME VA;5;Medium;False;False;False;False;False
103562;Houston;A MAT;2;Low;False;False;False;False;False
4269936;Houston;A MAT;2;Very high;False;False;False;False;False
4261474;Houston;NS PE;4;High;False;False;False;True;False
4437754;Houston;AN DE;5;Medium;False;False;False;False;False
136620;Houston;NE MA;1;Low;False;False;False;False;False
1520572;Houston;RIE T;2;Medium;False;False;False;False;False
2114018;Washington;N RAS;4;High;False;False;False;True;False
4415920;Washington;N RAS;5;Medium;False;False;False;False;False
768660;Washington; RUYS;4;High;False;False;False;False;False
3003278;Washington;E VER;5;Medium;False;False;False;False;False
4398290;Washington;N RAS;8;High;False;False;False;False;False
4555820;Washington;ENZO ;12;Medium;False;False;False;False;False
4603178;Washington;EN RO;2;Low;False;False;False;False;False
4400776;Washington;DE BE;5;Medium;False;False;False;False;False
4554688;Washington;AEL D;2;Medium;False;False;False;False;False
4558030;Washington;TJE D;4;Medium;False;False;False;False;False
3097030;Washington;ANNAH;2;Medium;False;False;False;False;False
2586750;Washington;TA BO;3;Medium;False;False;False;False;False
4597256;Washington;Y MOY;3;Medium;False;False;False;False;False
2318172;Washington;E LEB;2;Low;False;False;False;False;False
4306420;Washington;EL PF;2;Medium;False;False;False;False;False
1690240;Washington; VERC;3;Medium;False;False;False;True;False
3127700;Washington;CHA B;4;Very high;False;False;False;False;False
2957422;Washington;UILA ;2;Low;False;False;False;False;False
2429140;Washington;DE BE;5;Medium;False;False;False;False;False
3110038;Washington;E VER;4;High;False;False;True;False;False
2938984;Washington;TJE D;1;Very high;False;False;False;False;False
3027212;Washington; DE F;2;Medium;False;False;False;False;False
2169132;Washington;TJE D;3;Medium;False;False;False;False;False
4763936;Washington;VAN K;10;Medium;False;False;False;False;False
4598616;Washington;ARDE ;2;Medium;False;False;False;False;False
3082148;Washington;AEL D;2;Low;False;False;False;False;False
4561614;Washington;IENNE;2;Medium;False;False;False;False;False
4570104;Houston 2;INE C;9;Very high;False;False;False;False;False
4568434;Houston 2;-PIER;6;High;False;False;False;False;False
4569438;Houston 2;N MIC;4;High;False;False;False;False;False
3088272;Houston 2;ARDE ;5;High;False;False;True;False;False
2794686;Houston 2;NE VA;4;Very high;False;False;False;False;False
2207874;Houston 2;DIA A;3;High;False;False;False;False;False
2791098;Houston 2; WALR;5;High;False;False;False;False;False
3242912;Houston 2;N DE ;5;Medium;False;False;False;False;False
4572048;Houston 2;SPIRI;2;High;False;False;False;False;False
3113244;Houston 2;SPIRI;2;High;False;False;False;False;False
4569944;Houston 2;Y PAR;5;Medium;False;False;False;False;False
4564062;Houston 2;ID DE;2;High;False;False;False;False;False
3187544;Houston 2;ARDE ;4;Very high;False;False;False;False;False
3052716;Houston 2;IE LA;4;Very high;False;False;True;False;False
4341924;Houston 2;E VAN;5;High;False;False;False;False;False
2965112;Houston 2;N MIC;5;High;False;False;False;True;False
4564640;Houston 2;MED B;9;High;False;False;False;False;False
4571980;Houston 2;IE LA;2;High;False;False;False;False;False
1675192;London;ID DE;3;Medium;False;False;False;False;False
4465780;London; GODT;2;Medium;False;False;False;False;False
4574962;London; VAN ;2;High;False;False;False;False;False
4584790;London; DE B;2;High;False;False;False;False;False
4129604;London;N DE ;5;High;False;False;False;False;False
4584734;London;Y PAR;5;Medium;False;False;False;False;False
4563174;London;RIEN ;2;High;False;False;False;False;False
4281934;London;N THE;5;Medium;False;False;False;False;False
2449522;London;ANNE ;2;Medium;False;False;False;False;False
4588430;London;N THE;5;High;False;False;False;False;False
1552918;London;N TEM;5;Very high;False;False;False;False;False
3004512;London; ALBE;5;Medium;False;False;False;False;False
4025238;London;EL ME;2;Very high;False;False;False;True;False
4590928;London;EL ME;4;High;False;False;True;False;False
2086314;London;E VAN;3;High;False;False;False;False;False
4188406;London; ANDR;2;High;False;False;False;False;False
4586020;London;Y JAC;2;Very high;False;False;False;False;False
1597938;London;-PIER;2;Medium;False;False;False;False;False
4576218;London;EEN B;2;High;False;False;False;False;False
4592578;London; WALR;4;Very high;False;False;True;False;False
4008842;London;ID DE;5;Very high;False;False;True;False;False
4587542;London;RIEN ;2;Medium;False;False;False;False;False
4573960;London;ID DE;5;High;False;False;False;False;False
4585144;London;N DE ;2;Medium;False;False;False;False;False
4286370;London;N THE;5;High;False;False;False;False;False
4591594;London;Y JAC;2;Medium;False;False;False;False;False
2809910;London; DE B;1;Medium;False;False;False;False;False
4113930;London;DIA A;4;Very high;True;False;False;False;True
4586826;London;EL ME;6;Very high;True;False;False;False;True
2741786;London;E VAN;1;Very high;False;False;False;False;False
1690828;London;ANNE ;10;High;False;False;False;False;False
4591968;London;E DEC;6;Medium;False;False;False;False;False
4585832;London;ID DE;3;Very high;False;False;False;False;False
2333102;London;MED B;12;Very high;False;False;False;False;False
2160478;London;-PIER;5;Medium;False;False;False;False;False
4577286;London;-PIER;2;Medium;False;False;False;False;False
4073418;London;EL ME;5;High;False;False;False;False;False
4568448;London;-PIER;2;High;False;False;False;False;False
4573682;London;Y JAC;4;High;False;False;False;True;False
4302808;London;N THE;3;High;False;False;False;False;False
4087782;London;WALRA;4;Very high;False;False;False;False;True
2614856;Miami; DE R;2;Medium;False;False;False;False;False
2665566;Miami;DOLIE;2;Medium;False;False;False;False;False
2673844;Miami;EN DE;2;Very high;True;False;False;False;False
4536536;Miami;NE DE;2;Medium;False;False;False;False;False
4514372;Miami;STOPH;2;High;False;False;False;False;False
4809066;Miami;-MARI;2;Very high;False;False;True;False;False
2885800;Miami; DE C;3;Very high;True;False;False;False;False
4515970;Miami;Y STR;5;High;False;False;False;False;False

[Jero]

Ok, I will try to provide some sample data in the next post. Being new to this forum, I do appreciate you correcting me if I'm going about things the wrong way.

As for the visualisations:

The "Error category" chart:

• Axis: tblMain[ErrorCategory]
• Value: %GT Count of tblMain[PrimaryKey]

The "Severity" chart: In order to display the different severities in their logical order, I have created a secondary table tblSortOrder, which has in one column (called SeveritySort) each of the four possible values (Low, Medium, High, Very High), in another column (called SortOrder) the corresponding sort order (1, 2, 3, 4). The Severity column of the tblMain table is linked with the SeveritySort column of the tblSortOrder table through a many-to-one relationship.

• Axis: tblSortOrder[SeveritySort]
• Value: %GT Count of tblMain[PrimaryKey]

The "Impact(s)" funnel chart:

Values:

• CountNonRecuperableLoss = CALCULATE(COUNT(tblMain[PrimaryKey]);tblMain[NonRecuperableLoss]=TRUE())
• CountRecuperableLoss = CALCULATE(COUNT(tblMain[PrimaryKey]);tblMain[RecuperableLoss]=TRUE())
• CountPotentialFutureLoss = CALCULATE(COUNT(tblMain[PrimaryKey]);tblMain[PotentialFutureLoss]=TRUE())
• CountGain = CALCULATE(COUNT(tblMain[PrimaryKey]);tblMain[Gain]=TRUE())
• CountPotentialFutureGain = CALCULATE(COUNT(tblMain[PrimaryKey]);tblMain[PotentialFutureGain]=TRUE())

[Jero]

Thank you for your reply. Unfortunately, changing the chart type does not solve the issue.

Furthermore, I should add that I have tried substituting the boolean data with their corresponding numeric values (0 and 1) in the query, avoiding the need for custom measures. This did not solve the problem either.

I assume I need to use custom DAX formulas, but have been unsuccesful in writing something that works.

[Greg_Deckler]

OK, can you give us a little more information about what each of the visualizations shows? I do not understand how you are getting to the numbers in your visualizations for Error Category, Impacts and Severity. If you could post some sample data in a format that can be copied and pasted into an Enter Data query and explain your formulas/aggregations, it would be much easier to recreate this and get to an answer.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Greg_Deckler]

If you change from a funnel chart to a bar chart or something, does it work then?