Matrix Tooltip with a HasOneValue function. How do I get it to generate related columns.

[briguin]

** Edit 12/6:
Based on comments of question clarity, I've updated my post and the remaining followup is at the bottom.  Upon review, the question isn't really a HasOneValue or Tooltip question, it's about how String columns act within the Value section of a matrix

*** Original

I think the use of HasOneValue in the tooltip makes this a challenge. I have something working but wanted to see if there was a simpler way.

Using 2 data tables

I created a matrix and corresponding tooltip to display information about the customer.

Since the matrix can be expanded or contracted, I'm trying to control the tooltip information using the HasOneValue function.  

My tooltip displays correctly if I create all needed tooltip columns in Table1 using Related(Table2...)
Example:
In Tbl1 I create a column Rt_Loc = Related(Tbl2.RtLocation)
In Tbl1 I create a column Rt_District = Related(Tbl2.RtDistrict)

etc..for each item I want to filter correctly in tooltip

Notice that TBL1 is in both parts of the HasOneValue Equations
ToolTip_LocNm = if(HASONEVALUE(TBL1[Cust_Nm]) , Max(TBL1[Rt_Loc]) ,"")

Since the tooltip filters behind the scenes, I couldn't figure out if I could make it reference different tables.

Can HasOneValue work if the columns are not in same table?
ToolTip_LocNm = if(HASONEVALUE(TBL1[Cust_Nm]) , Max(TBL2[RtLocation]) ,"")

@AlB @AllisonKennedy 

*** Edit 12/6:

To explain myself better I was working on a sample model and I think I found where I was going wrong. The question isn't really a HasOneValue or Tooltip question, it's about how String columns act within the Value section of a matrix

1) If you want to use a related String column from Table2 into the Values section of a Table1 matrix you have to create those related columns in Table 1.
2) If you try to just pull in Table2 string columns into the values portion you will just get the first value of the Table2 (Assuming First is applied)

[briguin · Accepted solution]

I posted an edit to my original question and I'm going to accept that as a solution.

The edit on the Top and Bottom of the original post is my best attempt to describe what I learned

[AllisonKennedy]

@briguin  Thanks for the update - is this now solved? Please mark it with a solution if so, that way others can benefit and find it easily. 

With the Values of any visual (including matrix) this will always AGGREGATE the column based on the grouping provided by the Rows/Columns (or Axis for column chart). As you have discovered, the default aggregation for string/text fields is 'First'. You can change this to 'Count' which is also really helpful for testing, and helps you see if the HASONEVALUE will be met. 

Now for your Table1/Table2 dilemma - again, the Values filed of any visual must come from the Fact table. If you put a Dimension column in Values and try to use a Fact or different Dimension table for Rows/Columns or slicer, it won't work: The Dimension table does NOT get filtered by the fact, nor by other dimension tables. This is because of cross-filter direction. Sometimes you can use the RELATED() function in your DAX to pull a dimension column into the fact table (this saves you having to physically move the column into table1 as you have done).

I'm not sure what your relationships look like between the tables, so hopefully this makes sense to you. 

Here are some references on what I've done to try and help explain some of what's happening here. Hopefully they will help you or others. 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html

Video course (it's for excel, but applies to Power BI and explains cross-filter direction): https://learn.radacad.com/course/excel-power-tools-reporting-made-easy/?ref=818

[AlB]

Hi @briguin 

I don't understand the question either. The syntax for HASONEVALUE is

    HASONEVALUE(<columnname>)

By definition, thus, its functionality is based on a one-column input parameter. So when you talk about "columns" in plural, what exactly are you looking to do? Check whether two different columns each have only one value? Something else?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AllisonKennedy]

@briguin I don't really understand your question, but measures will work across two tables as long as you have the correct relationships. You can also explore the ISINSCOPE function, not sure if that will help you here?

[briguin]

Thanks for the response. I edited my original question. After review, I think I've identified my real question and answer