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

Applying If statement to column after already aggregated in Table

Hi All,

  Hopefully I’m able to explain this situation clearly, I’m somewhat new.  I have a table like this

Product Code

Quarter

Searches

14753551

2019Q1

150

14753551

2019Q2

200

14753551

2019Q3

300

14753551

2019Q4

200

14754061

2019Q1

600

14754681

2019Q1

1100

 

I have a table in Power BI with Product Code and Sum of Searches as in the picture attached.  I want to apply an If statement AFTER it is already aggregated like the way it happens in an Excel pivot,  since the user can select whatever quarter(s) they want in a slicer. 

   So if the sum of seaches > 400 then should be  ‘yes!’.  But even if I try using calculate, Sumx, it is still considering the Quarter field in the if statement somehow.  The red box I highlighted should be yes!.   It is correct if I bring in the Quarter - see other picture.  Any ideas? Thank you!pbi_sample_wrong.pngpbi_sample_right.png

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

Hi @Anonymous 

try a measure

Measure = 
if(calculate(sum('Table'[Searches]);ALLEXCEPT('Table';'Table'[Quarter];'Table'[Product Code]))>400;"yes";"no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

3 REPLIES 3
az38
Community Champion
Community Champion

Hi @Anonymous 

try a measure

Measure = 
if(calculate(sum('Table'[Searches]);ALLEXCEPT('Table';'Table'[Quarter];'Table'[Product Code]))>400;"yes";"no")

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Thanks so much!   I just had to change ";" to ","  and exactly what I needed and I would not have figured this out any time soon.  Thanks again

az38
Community Champion
Community Champion

@Anonymous the symbol-delimiter depends on system localization.

cheer and good luck! welcome to pbi family 🙂

 

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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.