Show right value from Dim Table

[andhiii079845]

Hello,

i have the following problem. I have one dim and one fact Table. 

The dim table:

The fact table

In the report i want to show the Name with the right title. In the matrix it works fine. 

But if I choose the title in a tooltip, it show the wrong title. (Meier with "Bachelor" instead of "Master").

One solution will be to create a calculated column in the fact with the title but i thinking about how to solve it with DAX.
One other important thing is that in the real report is the "Sum of product" column in the bar chart a measure. So the solution has to work with a measure. Can someone give me please a hint? 

Thank you

Andreas 

[tamerj1 · Accepted solution]

Hi @andhiii079845 
Please try

Title =
MAXX ( Fact, RELATED ( Dim[Title] ) )

[andhiii079845]

Wow, is works! Thank you!

[andhiii079845]

It works fine in the matrix visual but not as a tooltip. I use now a workaround. I show the personal number & Title togehter in the real report via a calculated column.  Thank you for your help!

[johnt75]

The problem is that names have multiple titles - both Meier and Schulz have both Master and Bachelor as titles, so Power BI is choosing the first in alphabetical order. One possible solution would be to add a new column to the dim table to use as the sort order for the title. You could do this in Power Query as a conditional column, or you could add a column using DAX like

Title sort order =
SWITCH ( TRUE (), 'Table'[title] = "Master", 1, 'Table'[title] = "Bachelor", 2 )

and then set this as the sort order column for the title column

[andhiii079845]

Thank you for your reply. It is only the case that the ID 1 and 4 have the same Name. I thinking about to filter the visual via a measure. My ideas was to calculated for the ID in the Dim Table the countrows of the fact table and use  the measure in "Filters" (greater than 0). But I was not able to write the code for it. 

[johnt75]

Not sure if it will work as a filter in the way that you want but the measure would be

Num facts = COUNTROWS('Fact')