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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TexasStone
Frequent Visitor

Create Virtual Flag in Data with DAX Measure

This is something that is super easy to do with a calculated column, but I'm not sure how to implement the measure version in a live connection.

I can pull data into a table visual from the server like this:

Image1.JPG

 

What I'd like to do is return only the orders that contain product ABC and what each of the revenue is for any of the products on those orders. Desired Output:
Image2.JPG

Doing this is proving tricky to me. If I write a measure like this:

 

RevenueMeasure =
VAR OrdersWithABC =
    CALCULATETABLE (
        VALUES ( Orders[Order Number] ),
        FILTER ( Product, Product[Product Name] = "ABC" )
    )
RETURN
    CALCULATE ( [Revenue], Orders[Order Number] IN OrdersWithABC )

 

I get the following output when looking at Order 123 (just the revenue of the product itself with all of the other products on the order showing as blanks or may not appear in table visual at all)
Image3.JPG

If I modify the measure to be like the below:

 

RevenueMeasure =
VAR OrdersWithABC =
    CALCULATETABLE (
        VALUES ( Orders[Order Number] ),
        FILTER ( Product, Product[Product Name] = "ABC" )
    )
RETURN
    CALCULATE (
        [Revenue],
        Orders[Order Number] IN OrdersWithABC,
        REMOVEFILTERS ( Product[Product Name] )
    )

 

I get the following output, which changes Product ABC to be the total revenue of the order number but the other two products still show blanks or may not appear in table visual at all.

Image4.JPG

I feel like this should be pretty simple to accomplish. I just want to pass a list of order numbers through a filter condition (in this case if the order contains Product ABC, basically flagging orders that have the product) and see all of the regular details I'd normally be able to see about the order. Does anyone have any ideas or could help, I'd greatly appreciate it.

7 REPLIES 7
Sahir_Maharaj
Super User
Super User

Hello @TexasStone,

 

Can you please try this approach:

RevenueMeasure =
VAR OrdersWithABC =
    CALCULATETABLE (
        VALUES ( Orders[Order Number] ),
        FILTER ( Orders, Orders[Product Name] = "ABC" )
    )
RETURN
    IF (
        SELECTEDVALUE ( Orders[Order Number] ) IN OrdersWithABC,
        [Revenue]
    )

If you find that some products still do not appear, modify it slightly:

RevenueMeasure =
VAR OrdersWithABC =
    CALCULATETABLE (
        VALUES ( Orders[Order Number] ),
        FILTER ( Orders, Orders[Product Name] = "ABC" )  
    )
RETURN
    CALCULATE ( 
        [Revenue], 
        Orders[Order Number] IN OrdersWithABC,
        ALL ( Orders[Product Name] )
    )

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Both of these don't work. I'm not sure if it makes a difference, but it looks like you assumed everything lives in the Orders table but it does not. The Order Numbers live in the order table but the Products live in the Product Table. 

Want to reiterate this is a live connection. I can do this super easily if importing the data.

some_bih
Super User
Super User

Hi @TexasStone if it is possible in your "upstream" before Power BI, filter out data already like Product[Product Name] = "ABC", this way will make your life easier?

When you use part REMOVEFILTERS, it literally remove filters and show totals (in this case for ABS product).

Another option in your case could be using CALCULATETABLE after you "import" data into Power BI using simple filter link on link with example

 

 





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

Proud to be a Super User!






I don't think so. I think it's super easy for me to just return the Revenue associated only with Product ABC either by filtering the visual or creating the measure.

What I don't understand however is to see all of the product details of any order that contains ABC. It seems like I need to be able to filter to a list of order numbers that contain ABC and then pass that list of order numbers to another measure to return all of the results. 

Looking at the example you sent it seems like if I simply did calculatetable and filtered to product = ABC then I'd only be seeing those ABC product rows. I need to see all of the rows in the table for any order number containing ABC. So somehow I need to set a filter for ABC, but then also see products DEF, GHI in the results. Hopefully that makes sense.

mdaatifraza5556
Super User
Super User

flag_measures =
IF(
    CALCULATE(
        COUNTROWS(data),
        ALLEXCEPT(data, data[Order Number]),
        data[Product Name] = "ABC"
    ) > 0,
    1,
    0
)

Now, can filter out the flag which 0.

Screenshot 2025-01-29 125113.png

I don't think this really works like I want it to. When looking at an order it returns every possible product, so if looking at order 456 for example it would show ABC, DEF, GHI, JKL, etc. and then when I bring in the Revenue metric it's just blank for all of the products anyways. It was also pretty slow. 

mdaatifraza5556
Super User
Super User

flag_measures =
IF(
    CALCULATE(
        COUNTROWS(data),
        ALLEXCEPT(data, data[Order Number]),
        data[Product Name] = "ABC"
    ) > 0,
    1,
    0
)

Now, you can filter out the flag which is 0.

Screenshot 2025-01-29 122544.png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.