Return a Value based on Text Column in another Table

Hi 303 ,

You can achieve this in Power BI using a DAX measure that dynamically checks whether each person plays "Football" and returns the word "Football" if they do, otherwise, it returns blank. Since you're working with a Semantic Model and cannot use merges or calculated columns, the measure needs to evaluate the relationship between the two tables at runtime.

A good approach is to use the CALCULATE function to filter only the "Football" rows from the Sports table. The following DAX measure does this:

ShowFootball = 
VAR _HasFootball = 
    CALCULATE(
        SELECTEDVALUE(Sports[Sport]),
        Sports[Sport] = "Football"
    )
RETURN
    IF(NOT(ISBLANK(_HasFootball)), "Football", BLANK())

This measure works by checking if there is a row in the Sports table where the person's sport is "Football." If such a row exists, it returns "Football"; otherwise, it returns blank. The result can be placed in a Table Visual along with the Names column to display only "Football" for those who play it.

Alternatively, a more efficient way to check for the presence of "Football" is to count how many rows exist where the sport is "Football" and return "Football" only if the count is greater than zero:

ShowFootball =
IF(
    CALCULATE(COUNTROWS(Sports), Sports[Sport] = "Football") > 0,
    "Football",
    BLANK()
)

This approach ensures that the table visual remains clean, displaying "Football" only when applicable, without duplicating names or requiring any filtering.

Best regards,

Hi 303 

To achieve the desired outcome where only the specific sport (e.g., "Football") appears for each name, and others show as blank if they don't match, you can approach this with measures in Power BI. Since you're working with a semantic model and not doing merges or calculated columns, here’s how you can get the behavior you want:

Football Check = 
IF (
    COUNTROWS ( 
        FILTER (
            Sports, 
            Sports[Sport] = "Football" && 
            Sports[Name] = MAX ( Names[Name] )
        )
    ) > 0, 
    "Football", 
    BLANK()
)

• If you want to apply this logic to other sports dynamically (e.g., allow the user to choose a sport from a slicer), you can modify the measure to take the selected sport from the slicer and return the relevant sport name or blank.
• If you need this for multiple sports, you could create a similar measure for each sport or make the measure dynamic based on slicer selection.

This should resolve the issue you're facing and give you the flexibility to show only the desired sport or a blank when there is no match.

hi 303 ,

You can achieve this in Power BI (DAX) using a calculated measure that checks whether each person has Football in the Sports table.

DAX Measure Solution

Since you cannot use merges or calculated columns, you need a measure to dynamically check if a person plays Football:

Football_Sport =
VAR HasFootball = 
    CALCULATE(
        MAX(Sports[Sport]), 
        Sports[Sport] = "Football"
    )
RETURN
IF(HasFootball = "Football", "Football", BLANK())