Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |