Some time in the past I used to be visiting a buyer that requested if they will filter a question knowledge by a column from one other question in Energy BI. And I mentioned after all you may. On this put up I clarify how that may be achieved in Energy Question. The important thing level is to know reference a question and reference a column of that question in Energy Question. That is helpful when you’ve gotten a lookup desk that may be sourced from each supported knowledge supply in Energy Question and also you wish to filter the outcomes of one other question by related column within the lookup question. In that case, you’ll have a kind of dynamic filtering. So, everytime you refresh your mannequin if new information have been modified in or added to the supply of the lookup question, your desk will routinely embrace the brand new values within the filter step in Energy Question.

Referencing a Question

It’s fairly easy, you simply want to make use of the identify of the question. If the question identify incorporates particular characters like house, then you should wrap it with quantity signal and double quotes like #”QUERY_NAME”. So, if I wish to reference one other question, in a brand new clean question, then the Energy Question (M) scripts would appear to be beneath:

let
    Supply = Product
in
    Supply

Or one thing like

let
    Supply = #"Product Class"
in
    Supply

Referencing a Column

Referencing a column can be fairly easy. Once you reference a column you should point out the referencing question identify, defined above, together with the column identify in brackets. So, the format will appear to be #”QUERY_NAME”[COLUMN_NAME]. The result’s a listing of values of that individual column.

let
    Supply = #"Product Class"[Product Category Name]
in
    Supply

Filtering a Question Column with Referencing Column from One other Question

Filtering a column utilizing the question editor UI is pretty easy. You simply want to pick the wanted values from dropdown and it’s accomplished. However the question in that case is filtered with fixed values. So in case your reporting requirement adjustments sooner or later, you’ll must redo the filtering and refresh the question. Our situation is a bit completely different although, we wish to filter a column by values from one other column. I simply talked about earlier how simply you may reference a column from one other desk. I additionally talked about that the outcomes of that referencing can be a Record of values proper? So what we’re after is filtering a column by a listing of values. There’s a perform in Energy Question that makes it straightforward, List.Comprises(checklist, values).

I’d moderately clarify the remaining with a situation. I’ve a Product Subcategory desk containing descriptive knowledge of all product subcategories. The enterprise now has a reporting requirement that I’ve to filter the Product Subcategory names by knowledge from one other desk. The second desk incorporates solely accredited subcategories. The second desk identify is “Product Subcategory Lookup”. The info within the “Product Subcategory Lookup” is regularly up to date by the enterprise.

The one factor I must do is to do is to make use of the Record.Comprises perform like beneath:

Record.Comprises(#"Product Subcategory Lookup"[Approved Subcategory], [Subcategory Name])

If you happen to’re used to make use of the question editor UI then you may simply apply a filter to the [Subcategory Name], then change the code as beneath:

If you happen to’re extra hands-on and like writing the M codes then use the Superior Editor to kind the codes.

#"Filtered Rows" = Desk.SelectRows(#"PREVIOUS_STEP", every Record.Comprises(#"REFERENCED_TABLE"[REFERENCED_COLUMN], [COLUMN_TO_BE_FILTERED]))

For these of you who’re extra conversant in SQL, the above M code works just like the beneath SQL script (in case your supply is SQL Server):

SELECT productsubcategorykey          
       , productsubcategoryalternatekey 
       , [Subcategory Name]
FROM   DimProductSubcategory
WHERE  [Subcategory Name] IN (SELECT [Approved Subcategory] 
							  FROM [Product Subcategory Lookup])