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
MattConner
Frequent Visitor

Measure to create a list where results meet at least one of many criteria

I am trying to write a measure that will give me a list of Accounts based on three individual filters. Using the below table (Called "Account Data") as a reference, I would like to write a measure that generates a list of accounts that shows "Active Accounts" where an active account meets any of the following Criteria:

Greater than $0 YTD Revenue
and/or

Greater than $0 YTD Sales

and/or

Greater than 0 YTD Oppoertunities

 

The final list would be the following accounts:

Alpha

Bravo

Charlie

Delta

Echo

Foxtrot

Gamma

Juliet

Kilo

Lima

 

Hotel and Indigo are excluded because they meet none of the criteria, whereas the others all meet at least one

Account YTD Revenue  YTD Sales YTD Opportunities
Alpha $                         100.00 $                         200.000
Bravo $                         200.00 $                         300.000
Charlie $                         300.00 $                         400.000
Delta $                                  -   $                         100.002
Echo $                                  -   $                         200.003
Foxtrot $                                  -   $                                  -  5
Gamma $                                  -   $                                  -  4
Hotel $                                  -   $                                  -  0
Indigo $                                  -   $                                  -  0
Juliet $                         100.00 $                                  -  0
Kilo $                         200.00 $                         300.000
Lima $                         300.00 $                         500.000
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @MattConner ,

 

Looking at you data assuming that the 3 columns you present are columns and not measures you should create the following code:

 

Account List = IF(SUM('Table'[ YTD Revenue ]) +SUM('Table'[YTD Opportunities]) + SUM('Table'[ YTD Sales ]) > 0 ; 1 ; 0)

Then use it as a filter on a table visual.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

Hi @MattConner ,

 

Looking at you data assuming that the 3 columns you present are columns and not measures you should create the following code:

 

Account List = IF(SUM('Table'[ YTD Revenue ]) +SUM('Table'[YTD Opportunities]) + SUM('Table'[ YTD Sales ]) > 0 ; 1 ; 0)

Then use it as a filter on a table visual.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.