Measure with complex filter context

[minmin99]

Hi everyone,

I am following a Salesforce report logic to recreate a power bi report. I would like to create a measure shown in a card that has partly different filters than other visuals on the page. 

The problem that I am facing is that I would like to remove some filters existing on the page and introduce new fields from the same tables in which existing filters are located. All fields come from 3 related tables and data models are shown at the end of my description.

What I would like to achieve:

Opportunity Table:

Remove Filters: Forecast Closed, Pilot Quote

Keep filters: Stage

Add filters: Primary Win Loss Reason, Primary Competitor

Opportunity product Table:

Remove filters: Bookings Category

Keep filters: Product Family, Bookings Type

Date:

Keep filter: Year-Quarter

The measure I am trying:

testing = CALCULATE(SUM('Opportunity Product'[NNARR]), FILTER(ALL(Opportunity), (Opportunity[Stage] = "Closed Lost" || Opportunity[Stage] = "CLosed Won") && (Opportunity[Primary Win Loss Reason] = "Closed Lost to Competitor" || Opportunity[Primary Win Loss Reason] = "Won") && Opportunity[Primary Competitor] <> ""), KEEPFILTERS(VALUES('Opportunity Product'[Product Family])), KEEPFILTERS(VALUES('Opportunity Product'[Bookings Type])), KEEPFILTERS(VALUES('Date'[Year-Quarter])))

But it is wrong...

Here is the filter for the measure based on SF report:

Here are the existing filters for the page:

All fields come from 3 related tables:

Fields used in opportunity table:

Stage; Primary Win/Loss Reason; Primary Competitor; Forecast Closed; Pilot Quote

Fields used in opportunity Product table:

Bookings Type; Bookings Category; Product Family

Field used in Date table:

Year-Quarter

Thanks!

[edhans]

Don't know about anyone else, but I'm gonna need a lot more info, or a PBIX file. For reference here is your measure formatted to read:

testing =
CALCULATE(
    SUM( 'Opportunity Product'[NNARR] ),
    FILTER(
        ALL( Opportunity ),
        ( Opportunity[Stage] = "Closed Lost"
            || Opportunity[Stage] = "CLosed Won" )
            && ( Opportunity[Primary Win Loss Reason] = "Closed Lost to Competitor"
            || Opportunity[Primary Win Loss Reason] = "Won" )
            && Opportunity[Primary Competitor] <> ""
    ),
    KEEPFILTERS(
        VALUES( 'Opportunity Product'[Product Family] )
    ),
    KEEPFILTERS(
        VALUES( 'Opportunity Product'[Bookings Type] )
    ),
    KEEPFILTERS(
        VALUES( 'Date'[Year-Quarter] )
    )
)

This is complex enough even if you gave me every table in a pretty Excel spreadsheet, it would take me time to build the model and I might not still duplicate your situation. Perhaps someone else will jump in, but I'd need a PBIX file to work with.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[minmin99]

Hi,

Thanks for your reply! I created a sample pbix file. Could you help me take a look at it? 

sample data 

Thank you so much!

[dax]

Hi @minmin99 , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[minmin99]

Hi,

I created a sample pbix file that simplifies the real data. Could you help me take a look at it?

sample data 

Thanks!