cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Rubal Helper I
Helper I

If statement to be used with a measure used in a card

Hi,

I have used the below measures in card to identify the top 1 to 5 project name. 

 

However, I wish to add an if statement to return a 'Blank' as an output on the card if there is no 2nd /3rd/4th of 5th project. Currently, this measure  is returning the same project name between 1 to 5 even if there are only one project. It returns blank if there is no project and it works fine if there are more than 5 projects to identify the top 1 to 5.

 

With the table below when I select the country like Australia, I do get the top 5 project name (Alpha, Gamma, Apollo, Narellan, Aquaone) based on their project priority score.

However, for Fiji, I get a return of Beta, Delta, Bluedog, Bluedog ( i wish to proceed with an output of Beta, Delta, Bluedog, Blank) and for New Zealand, it returns Vitafresh, Vitafresh, Vitafresh, Vitafresh,  Vitafresh ( i wish to proceed with an output Vitafresh, Blank, Blank, Blank, Blank)  

 

Top Ranked Project = CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))
 
2nd Ranked Project = CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC)))
 
3rd Ranked Project = CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))
 
4th Ranked Project = CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC)))))
 
5th Ranked Project = CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,TOPN(5,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))))
 
Project NameProject Priority ScoreCountry
Project Alpha1Australia
Project Beta2Fiji
Project Gamma3Australia
Project Delta4Fiji
Project Apollo5Australia
Project Bluedog6Fiji
Project Narellan7Australia
Project Vitafresh8New Zealand
Project Aquaone9Australia

 

I am not sure if an if statement is the right way to go. I am happy to explorer other option as well to make this work.

Appreciate your help on this.

1 ACCEPTED SOLUTION

Accepted Solutions
Rubal Helper I
Helper I

Re: If statement to be used with a measure used in a card

The below seems to work fine

 

Test 4th Ranked Project = if(
CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC)))))=
CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))), "Blank",
"CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))))

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: If statement to be used with a measure used in a card

Rubal Helper I
Helper I

Re: If statement to be used with a measure used in a card

Thanks Amit but I couldn't find any relevance on what I am trying to achieve here.

Rubal Helper I
Helper I

Re: If statement to be used with a measure used in a card

The below seems to work fine

 

Test 4th Ranked Project = if(
CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC)))))=
CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))), "Blank",
"CALCULATE(SELECTEDVALUE('PowerBi Data Export'[Project Name]),TOPN(1,TOPN(2,TOPN(3,TOPN(4,ALLSELECTED('PowerBi Data Export'),'PowerBi Data Export'[Project Priority Score], ASC))))))

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors