power bi report builder
1 Topic
Filtering Table Based On Measure Value
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.Solved11KViews0likes2Comments