Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

IF function

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

 

Sample table as below: 

Capture1.JPG

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
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

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

Sample data:

1.PNG

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:

1.PNG

You could also download the pbix file to have a view.

 

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.

View solution in original post

5 REPLIES 5
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

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

Sample data:

1.PNG

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:

1.PNG

You could also download the pbix file to have a view.

 

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.
Anonymous
Not applicable

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. 

 

Capture3.JPG

 

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, 

Hi @Anonymous ,

It's pleasant that your problem has been solved, could you please mark my reply as Answered to close this topic?

 

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.
Greg_Deckler
Super User
Super User

Perhaps:

 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.