Forum Discussion

andhiii079845's avatar
andhiii079845
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

Show 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 m...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi andhiii079845 
    Please try

    Title =
MAXX ( Fact, RELATED ( Dim[Title] ) )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
andhiii079845
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

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

    Num facts = COUNTROWS('Fact')