Query on Excel list with parameter

[WX]

Hi,

I'm new to Power BI and need some help or just get to know if the problems I try to solve.

I have a table, which source is a Excel file and contains 4 columns (Name, Name #2, Age and Category). In my dashboard I show this list. A user should now be able to select a user [1]. Each user has different categories [2] and for each category I want to show a list of users containig this category [3] and [4].

I already learned that I can use measure to get the values selected in the list. In this case my measure would return B, D. The problem is now that I do not know how to use a measure as an input parameter as a filter on a Excel list.

Is this possible to realise in Power Bi Desktop?

Thanks!

[WX]

Hi @FrankAT  and @amitchandak , thanks for your answers so far.

let me explain my two problems again:

1. filtering of categories:
   I'm already working at the extended editor with a syntax like:
   Table.SelectRows(source, each Text.Contains([Category], "B"))
   However I do not know how to dynamically insert the "B". Any ideas?
   I know that creating a new row for each category would be a proper solution, however the data will grow by 50k lines per year and there can be up to 20 categories selected. So the amount of data would explode ...
   
   
2. dynamically show tables:
   In case of the selection of yellow row there should appear 2 tables at the dashboard, one for category "B" and one for "D". In case of the selection of row containing "Bobby" there should be 4 tables at the dashboard, for each category "D", "E", "F" and "G". Is this possible in Power BI to show/hide tables, which are not in use? 

Thanks!

[Anonymous]

Hi @WX 

I think you want to show all Users who contains category like the user you select. We can't build dynamic table but we can build measures to show dynamic results in table visuals.

I build a table like yours to have a test.

Firstly we duplicate the Table and transform it, splite Category column by Delimiter "," into row and delete other columns except Name and Category. Then rename Category column as Value.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3MTlXSUQoBYiMjIOGo46TjrBSrE60UXFqcmAcUCQZiE1Mg4aTjApZwyk9KqgTyfYHY3AJIuOi46rjpuCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Name# 2" = _t, Age = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Name# 2", type text}, {"Age", Int64.Type}, {"Category", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Category", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Category"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Age", "Name# 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Category", "Value"}})
in
    #"Renamed Columns"

Result:

Build relationships between Name columns in two table.

Build a Name table to build a slicer.

Name = VALUES('Table'[Name])

Build measures:

Measure = 
Var _select = SELECTEDVALUE('Name'[Name])
Var _T1 = CALCULATETABLE(VALUES(RefrValue[Value]),RefrValue[Name]=_select)
Var _Table = SUMMARIZE(RefrValue,RefrValue[Value])
Return
IF( _T1 in _Table,1,BLANK())

Name# 2 = CALCULATE(MAX('Table'[Name# 2]),FILTER('Table','Table'[Name]=MAX(RefrValue[Name])))

Age = CALCULATE(MAX('Table'[Age]),FILTER('Table','Table'[Name]=MAX(RefrValue[Name])))

Category = CALCULATE(MAX('Table'[Category]),FILTER('Table','Table'[Name]=MAX(RefrValue[Name])))

Build a table visual and Drag "Measure" into Filter on this visual to show items =1.

Result is as below.

Show blank as default.

Select Susan in Slicer.

You can download the pbix file from this link: Query on Excel list with parameter

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@WX , Not sure I got it.

Create a table That contains all categories A,B,C ... One in each row

Use that for slicer but do not join with Table

calculate(count(Table[name), filter(Table, CONTAINSSTRING(Table[Category], selectedvalue(Category[Category]))))

[FrankAT]

Hi @WX 

you can do it like this:

See pbix files attached

Regards FrankAT