Forum Discussion
Solution SageShow right value from Dim Table
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
Hi andhiii079845
Please tryTitle = MAXX ( Fact, RELATED ( Dim[Title] ) )
6 Replies
- tamerj1
Community Champion
Hi andhiii079845
Please tryTitle = MAXX ( Fact, RELATED ( Dim[Title] ) )
andhiii079845Wow, is works! Thank you!
johnt75Super User
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')
- 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!