customize query that populates list of values used in filter pane

[TonyR_Beacon]

To set the stage - The PBI data source is a LiveConnection to SSAS Tabular model.  The Tabular model has 3 fact tables and 10 dimension tables.  All dimensions are joined to all fact tables.

The Issue - The list of values used for picklist in filter pane returns all distinct records from a dimension object. 

The Question - Is there a way to customize the filter list of values so that it queries a specific fact table when generating the LOV for user selection?

Example - Fact table 1 has records that link to the Zip dimension with zipcodes 12345 and 54321.  Fact Table 2 has records that link to the Zip dimension with zipcodes 12345, 98765, & 56701.  Fact Table 3 has records that link to the Zip dimension with zipcodes in 12345 and 67890.  

When the zip code is added to the filter pane, the LOV pulls all zipcodes.  Is it possible to limit the LOV to only allow zipcodes in Fact Table 2 to show in the LOV.  In the example above, the LOV should have 12345, 98765, & 56701, but would not include 54321 or 67890.

[v-juanli-msft]

Hi @TonyR_Beacon 

With live connection, we can't make relationships among these tables.

As tested, when i add zip from Zip dimension in the slicer, it can't slicer the data in three fact tables.

If you want to only zipcodes in Fact Table 2 to show in the LOV, just add "zip" from Fact Table 2 in the slicer.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.