Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure to return rows based on column conditions

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. Eac...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

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.