Return a Value based on Text Column in another Table

[303]

Hello,

Can't get my head around this one, I'm working with a Semantic Model so no merges or calculated columns. Below is a simplified example. Lets say I have two tables, one called Names and one called Sports.

Names

Bob

Rita

Sue

Charlie

Name	Sport
Bob	Football
Bob	Rugby
Bob	Cricket
Rita	Rugby
Rita	Cricket
Sue	Running
Sue	Snooker
Charlie	Football
Charlie	Cricket

What I want to produce is a table(visual) like below, so if they play football the word comes up, but if they don't I just get a blank, so I can't just filter the field in the table. All I want to see is the word Football which is selected from the Sports table or a blank if there is nothing. At the moment all I get is either multiple lines with everything on or I have to filter out the ones which don't have a match. Any help greatly appreciated to get me going in the right direction.

Names	Sport
Bob	Football
Rita
Sue
Charlie	Football

[DataNinja777 · Accepted solution]

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,

[Poojara_D12 · Accepted solution]

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.

[rohit1991 · Accepted solution]

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())

[Poojara_D12 · Accepted solution]

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.

[DataNinja777 · Accepted solution]

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,