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

Help crossfiltering table visual on different columns

Hi all,

 

I'm having trouble with something that seems like it should be very simple. 

I have a database of classes, instructors, and scores from evaluation surveys (among other things). I need cards to show averages from a few different things based on the selected row in a table visual.

We'll call the relevant columns main[class name], main[instructor name], main[instructor avg] (calculated column), main[overall avg] (calculated column that includes instructor avg as well as a few other metrics).

 

Here's a mockup of the table viz:

ClassInstructor
Game Design 4/14/20Mario Brothers
Technical Drawing 9/9/20Bill DaBridge
Beatboxing 10/15/20Ricky Bootsncats
Plumbing 8/12/20Mario Brothers

 

This is the easy part.

I need cards to show the instructor avg and overall avg for the selected class in the table viz. Like so:

 

Selected Class: Game Design taught by Mario Brothers

Class Instructor Avg

 

8.5

Class Overall Avg

 

8.1

_______________________________________________________________________

The hard part is that I also need cards to show the OVERALL instructor avg and overall avg across all the classes that instructor has taught. Like so:

 

Selected Trainer: Mario Brothers

Overall Instructor Avg

 

9.7

Overall Overall Avg

 

9.5

 

In other words, I need to crossfilter two of my cards on the selected table row, and I need to crossfilter two cards based only on the instructor value from the selected row.

I've been able to use things like Measure1 = SELECTEDVALUE(main[instructor name], "NA") to isolate the instructor value from the table viz, but I can't figure out where to go from there.

 

I've tried things like

 

Measure2 = VAR inst = Measure1

RETURN

           CALCULATE (

                        AVERAGE ( main[instructor avg] ),

                         main[instructor name] = inst )

 

But it's not working for me (I know this syntax may not be 100% accurate--this is just off the top of my head). I'm still ending up with my Measure2 on the card showing either the averages only for the selected table row OR the entire avg across all instructors (depending on whether I limit visual interactions).

 

By the way the reason I'm using a table viz and not a slicer is so that the user will be able to easily see which classes were taught by which instructors.

 

Is this way harder than it should be, or am I just missing a silly little thing?

 

Unfortunately, I can't post the file, and it would take a lot of work to sanitize the data and create a mockup, so hopefully this makes sense. Please let me know if I need to clarify anything.

 

Any help would be deeply appreciated. Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @jp8888 

 

I'm not very clear about the expected result. You may try the following measure to see if it works.

 

 

Result =
VAR inst = [Measure]
RETURN
    CALCULATE (
        AVERAGE ( main[instructor avg] ),
        FILTER ( ALL ( 'main' ), main[instructor name] = inst )
    )

 

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

Allan,

 

Your suggestion does exactly what I need it to do. Thank you!

 

I actually figured out a solution of my own very very shortly before seeing your post. I ended up creating an additional table containing only [instructor name] (grouped) and [instructor avg] and [overall avg] (both summarized on average then joined to 'main' on [instructor name].

 

However, I think your solution is a bit more elegant. I learned something new from this.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Hi, @jp8888 

 

I'm not very clear about the expected result. You may try the following measure to see if it works.

 

 

Result =
VAR inst = [Measure]
RETURN
    CALCULATE (
        AVERAGE ( main[instructor avg] ),
        FILTER ( ALL ( 'main' ), main[instructor name] = inst )
    )

 

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

Allan,

 

Your suggestion does exactly what I need it to do. Thank you!

 

I actually figured out a solution of my own very very shortly before seeing your post. I ended up creating an additional table containing only [instructor name] (grouped) and [instructor avg] and [overall avg] (both summarized on average then joined to 'main' on [instructor name].

 

However, I think your solution is a bit more elegant. I learned something new from this.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors