Measure to return rows based on column conditions

[Kcloud]

Hi All,

I am trying to retrieve / filter rows in a report based on a where clause.

The scenario is that each quote (Id) will have an Estimate and when work has been done, will have a Final.

Each Id can additionally have multiple types based on the Rate (Work carried out during Day rates or Night Rates)

I want to be able to filter for a report and retrieve the rows where the Type is Final if the Id has a Final, or retrieve Estimate where it doesn't.

My imported data looks like this:

What I want to display in my report:

[amitchandak]

@Kcloud , Use this measure as visual level filter

countx(filter(summarize(Table, Table[QuoteID], Table[Type], "_1", countx(filter(allselected(Table), Table[ID], max(Table[ID]) && Type = "Final")),, "_2", countx(filter(allselected(Table), Table[ID], max(Table[ID]) && Type = "Estimated"))), (not(Isblank(_1)) && [Type] ="Final") || ((Isblank(_1)) && [Type] ="Estimated")), [QuoteID])

check for not blank. Else create all measure using this code

[Kcloud]

Unfortunately I am receiving this error when using that measure as a filter:

[v-rzhou-msft · Accepted solution]

Hi @Kcloud ,

I create a sample to have a test. My sample is the same like yours.

Measure:

Filter Measure = 
VAR _Type_List =
    CALCULATETABLE (
        VALUES ( 'Table'[Type] ),
        ALLEXCEPT ( 'Table', 'Table'[QuoteId] )
    )
RETURN
    IF (
        "Final" IN _Type_List,
        IF ( MAX ( 'Table'[Type] ) = "Final", 1 ),
        IF ( MAX ( 'Table'[Type] ) = "Estimated", 1 )
    )

Create a table visual , add this measure into visual level filter and set it to show items when value =1.

Result is as below.

Best Regards,
Rico Zhou

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