Create Virtual Flag in Data with DAX Measure

[TexasStone]

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:

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:

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)

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.

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.

[Sahir_Maharaj]

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] )
    )

[TexasStone]

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]

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

[TexasStone]

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]

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.

[TexasStone]

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]

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.