Filtering Table Based On Measure Value

Anonymous — Wed, 30 Oct 2019 01:09:04 GMT

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/false#M242748

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.

Re: Filtering Table Based On Measure Value

Anonymous — Wed, 30 Oct 2019 23:27:59 GMT

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)

Re: Filtering Table Based On Measure Value

Anonymous — Fri, 19 Feb 2021 16:39:38 GMT

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] } ) )

topic Filtering Table Based On Measure Value in DAX Commands and Tips

Filtering Table Based On Measure Value

Re: Filtering Table Based On Measure Value

Re: Filtering Table Based On Measure Value