Filtering Table Based On Measure Value

[danderson2692]

Hello,

I'm running into some issues while trying to parameterize a query in Power BI Report Builder. For the parameters, I need to be able to select multiple values so I can't use conventional filter expressions like Filter(<Table>,[Field]="LookupValue"). I've found how to get this to work for columns that exist in the raw data but am having trouble with values calculated from measures.

Here's the relevant code that I'm attempting to use:

VAR StatusCount =
    PATHLENGTH ( @ProjStatus)
VAR StatusNumberTable =
    GENERATESERIES ( 1, StatusCount, 1 )
VAR StatusTable =
    GENERATE (
        StatusNumberTable,
        VAR StatusCurrentKey = [Value]
        RETURN
            ROW ( "Key", PATHITEM ( @ProjStatus,StatusCurrentKey ) )
    )
VAR GetKeyStatusColumn =
    SELECTCOLUMNS ( StatusTable, "Key", [Key] )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Projects'[Project Name],
      "WorstStatus", 'Meta_EngStatusSummary'[WorstStatus]
    )

EVALUATE
 CALCULATETABLE( __DS0Core,INTERSECT( SELECTCOLUMNS ( __DS0Core, "Key", [WorstStatus] ) , GetKeyStatusColumn))

[WorstStatus] is a measure with possible values "Green", "Yellow", and "Red" FWIW.

The first 4 variables are used to convert the input string ("Green|Yellow") to a single-column table (GetKeyStatusColumn) which I'm trying to use to filter __DS0Core. The issue that I'm running into is that CALCULATETABLE is not filtering __DS0Core based on the filter applied. I'm trying to mimic the approach used here: https://community.powerbi.com/t5/Desktop/Filter-table-based-on-another-table/m-p/519101/highlight/fa... 

Looking at the values of GetKeyStatusColumn and the SELECTCOLUMNS that I'm using to pull the WorstStatus column, both tables appear correctly/as expected on their own. I believe the issue is that the SELECTCOLUMNS breaks the relationship between it and __DS0Core, but I may be wrong. When trying to use [WorstStatus] directly without SELECTCOLUMNS, an error is thrown that it is not a table.

Any suggestions on how to try to correct this or another approach to test would be greatly appreciated.

Thanks ahead of time.

[danderson2692 · Accepted solution]

I looped back to a result which worked with single values ( FILTER( __DS0Core,'Meta_EngStatusSummary'[WorstStatus2] =@ProjStatus) ) and realized I was over-thinking this... This is the EVALUATE statement which ended up working for me:

FILTER( __DS0Core,'Meta_EngStatusSummary'[WorstStatus2] IN GetKeyStatusColumn)

[Anonymous]

Thanks a lot, I had similar problem and your approach gave me the solution:

Measures:

    Current Week = WEEKNUM(TODAY() , 21 )

Total Planned Week WO =
    CALCULATE ( COUNT ( 'WO'[WO Number] ),
    WO[WO Status] IN { "MH", "MJ", "MK" },
    FILTER( WO,
                 WO[Planned Week] IN { [Current Week] } ) )