Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi @Anonymous
A very basic measure might look like this
Measure = CALCULATE( MAX('SalesTable'[TeamName]), 'SalesTable'[SalesPerson]="Bill Amis" )
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])
Hi @Anonymous
A very basic measure might look like this
Measure = CALCULATE( MAX('SalesTable'[TeamName]), 'SalesTable'[SalesPerson]="Bill Amis" )
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.
Hi @Anonymous
Please try this
Measure = CALCULATE( MAX('SalesTable'[TeamName]), FiLTER( ALL('SalesTable'[SalesPerson]), 'SalesTable'[SalesPerson]="Bill Amis" ) )
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])
Thank you!
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |