DAX equivalent of SELECT TOP 1 FROM [table] WHERE [table].[columnName] = "something"

[Anonymous]

I am attempting to do some RLS through Power BI, and I am looking for the DAX equivelent to a similar SQL expression below.

SELECT TOP 1 [SalesTable].[TeamName] 

FROM [SalesTable]

WHERE [SalesTable].[SalesPerson] = 'Bill Amis'

I am trying to use "FIRSTNONBLANK" but its not returning the results I am looking for. Particularly I am not sure how to integrate the filter of [SalesTable].[SalesPerson] = 'Bill Amis' in the expression. 

[Phil_Seamark · Accepted solution]

Hi @Anonymous

A very basic measure might look like this

Measure = 
    CALCULATE(
        MAX('SalesTable'[TeamName]),
        'SalesTable'[SalesPerson]="Bill Amis"
        )

[Phil_Seamark · Accepted solution]

Hi @Anonymous

A very basic measure might look like this

Measure = 
    CALCULATE(
        MAX('SalesTable'[TeamName]),
        'SalesTable'[SalesPerson]="Bill Amis"
        )

[Anonymous]

Is there a way to do it as the following?

Measure = 
    CALCULATE(
        MAX('SalesTable'[TeamName]),
        ALL('SalesTable'[SalesPerson])="Bill Amis"
        )

I am looking for a whole table search, rather than a row specific one. So for every row in my data, I want the result of  'SalesTable'[TeamName] for "Bill Amis" rathar than just the rows "Bill Amis" is in. 

[Phil_Seamark]

Hi @Anonymous

Please try this

Measure = 
    CALCULATE(
        MAX('SalesTable'[TeamName]),
        FiLTER(
            ALL('SalesTable'[SalesPerson]),
            'SalesTable'[SalesPerson]="Bill Amis"
            )
        )

[Anonymous · Accepted solution]

Close! A little bit of adjustment got me there.

Measure = 
    CALCULATE(
        MAX('SalesTable'[TeamName]),
        FiLTER(
            ALL('SalesTable'),
            'SalesTable'[SalesPerson]="Bill Amis"
            )
        )

This got me the result that I was looking for. Every row came back the team name of Bill Amis. 

Just a note for anyone in the future. I was doing this so that I could do a DAX expression so that if a User as viewing the report, we can figure out if they are a leader of a team, and therefore able to see all the team members data in the report. 

(This is so we don't have to use roles)

CALCULATE(
        MAX('Flattenedhierarchy'[groupName]),
                FILTER(
            ALL('Flattenedhierarchy'), Flattenedhierarchy[Name]= USERNAME())
        ) = Flattenedhierarchy[groupName])

[Anonymous]

Thank you!