Remove a column from report filter and add more filters in measure - Quote and Order

[Jill]

Hi I have table with columns

Quote_Num, Quote_EntryDate, Sales Type, Product Line, Quote_Status, Order_Date.

The report has filter on Quote_EntryDate, Sales Type and Product Line.

I try to create a calculation that count the number of win within this year regardless Quote_EntryDate.

CALCULATE(DISTINCTCOUNT(QuoteHistoryReport[Quote_Num]),FILTER(QuoteHistoryReport,QuoteHistoryReport[Quote_EntryDate].[Date]>=MIN(QuoteHistoryReport[Quote_EntryDate])),FILTER(QuoteHistoryReport,QuoteHistoryReport[Quote_Status]="W"),FILTER(QuoteHistoryReport,QuoteHistoryReport[Order_Date].[Year]=YEAR(TODAY())))

however this return result still has Quote_EntryDate filtered.

Any clue?

[Icey · Accepted solution]

Hi @Jill ,

Do you mean that "Quote_EntryDate" column is in the field "Filters on this report" and you want the new measure to ignore this report level filter?

If so, try this:

Measure =
CALCULATE (
    DISTINCTCOUNT ( QuoteHistoryReport[Quote_Num] ),
    FILTER (
        ALL ( QuoteHistoryReport ),
        QuoteHistoryReport[Quote_EntryDate].[Date]
            >= MIN ( QuoteHistoryReport[Quote_EntryDate] )
    ),
    FILTER ( ALL ( QuoteHistoryReport ), QuoteHistoryReport[Quote_Status] = "W" ),
    FILTER (
        ALL ( QuoteHistoryReport ),
        QuoteHistoryReport[Order_Date].[Year] = YEAR ( TODAY () )
    )
)

Best Regards,

Icey

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

[Fowmy]

@Jill 

Try:

Measure =
CALCULATE (
    DISTINCTCOUNT ( QuoteHistoryReport[Quote_Num] ),
    FILTER ( QuoteHistoryReport, QuoteHistoryReport[Quote_Status] = "W" ),
    FILTER (
        QuoteHistoryReport,
        QuoteHistoryReport[Order_Date].[Year] = YEAR ( TODAY () )
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[dedelman_clng]

Hi @Jill  - try this

NumWins =
CALCULATE (
    DISTINCTCOUNT ( QuoteHistoryReport[Quote_Num] ),
    FILTER (
        ALL ( QuoteHistoryReport ),
            QuoteHistoryReport[Quote_Status] = "W" && 
               QuoteHistoryReport[Order_Date].[Year] = YEAR ( TODAY () )
    )
)

ALL removes all filters from the table (including report-level filters) and then we just apply back what filters we do want applied.

Hope this helps

David