cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## IF function

Hi, I need help to calculate some sales but cannot work it out.

Sample table as below:

When "ranking by bill month" has value 1, all "invoice_amt" of "customer_id" is a "keep". The rest is not.

I cannot figure out an IF function for this. Appreciate any help or advice!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: IF function

Hi @phuynh242 ,

Based on my test, you could refer to below steps:

Sample data:

Create below measure:

```Measure = var a = CALCULATE(MAX('Table1'[id]),FILTER(ALL('Table1'),'Table1'[ranking by bill month]=1))
return IF(MAX('Table1'[id])=a,"Keep",BLANK())```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

## Re: IF function

Perhaps:

`IF([ranking by bill month] = 1,[ranking by bill month],BLANK())`

Proud to be a Datanaut!

Community Support Team

## Re: IF function

Hi @phuynh242 ,

Based on my test, you could refer to below steps:

Sample data:

Create below measure:

```Measure = var a = CALCULATE(MAX('Table1'[id]),FILTER(ALL('Table1'),'Table1'[ranking by bill month]=1))
return IF(MAX('Table1'[id])=a,"Keep",BLANK())```

Result:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: IF function

Hi @Greg_Deckler , thank you for your suggestion but this DAX only let me keep the "invoice_amt" of "customer_id" that has "ranking by bill month" =1; other "invoice_amt" of the same "customer_id" in this case will be blank. I need to include them all as long as a customer_id has a ranking 1 in their record.

Frequent Visitor

## Re: IF function

hi @v-danhe-msft , thank you so much for your help. However, when I tried it with a larger data set, it does not work correctly the way I need it. The field "ranking by bill month" actually resets per "id"; so different "id" will have the same ranking structure.

I tried your measure but it only works with id:2041. Basically, I just want to keep the "invoice_amt" of all "id" whenever the "id" has a 1 in the ranking by bill month".

I hope I explain well enough. I am still learning Power BI as I go.

I tried this measure below and it works now, credit to @. I will test it for the whole data set.

Measure =

IF (

"1"

IN CALCULATETABLE (

VALUES ( Sheet1[ranking by bill month] ),

ALLEXCEPT ( Sheet1, Sheet1[id])

),

"Keep",

"Not"

)

Thank you all,

Community Support Team

Hi @phuynh242 ,