cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BaakWu Regular Visitor
Regular Visitor

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

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. 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hi @BaakWu

 

A very basic measure might look like this

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

BaakWu Regular Visitor
Regular Visitor

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

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])
5 REPLIES 5
Super User
Super User

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

Hi @BaakWu

 

A very basic measure might look like this

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

BaakWu Regular Visitor
Regular Visitor

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

Thank you!

BaakWu Regular Visitor
Regular Visitor

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

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. 

Highlighted
Super User
Super User

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

Hi @BaakWu

 

Please try this

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

BaakWu Regular Visitor
Regular Visitor

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

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