Create Filter from Columntitle

[acg]

Hi, 

I have 6 different Customer types for 800 different entities over 4 years. For each entity the Customer Types sum up to 100%, and I have worked out the actual numbers of customers for each Customer Type per Entity. 

The aim is build a line chart showing the sum of each customer type across years. So far so good. 

However, I would like to be able to choose 1 Customer Type at a time. In essence a sliver. where I can choose customer Type 1 -6 and the line chart shows the change a Customer Type has undergone over time. Say for example, I wanted to check for "seasonal

workers", then I would see an uptick during the summer months but in general lower than normal number during the Covid Lockdowns. 

I noted that it is possible to create a hierarchy of Customer Types and put that hierarchy into a Slicer. But because we have 6 different Columns with different Customer Type Titles and how the number of specific customer type per entity, the hierarchy filter would only show numbers. 

What else can I do?

Example how it looks:

Needed is Slicer where I can choose between: 

Entity

Resident

Non-Resident

Saisonal

Full Time

Part-Time

Other

Parameters come to mind as well. But I did not manage to get the parameter working. I created a measure with the sum of each entity and would then create a parameter. 

But the visual remains empty, and I guess, what the parameter does is count the items in the fields. But while creating sums of the customer types, it can't count anything. So I am not sure what to do.  

Entity	Resident	Non-Resident	Saisonal	Full Time	Part-Time	Other
10	44	6	3	10	17	20
12	35	10	10	30	10	5
13	65	35	0	0	0	0
15	47	26	17	2	6	2
17	51	49	0	0	0	0
19	33	36	7	20	4	0

[MFelix]

Hi @acg ,

In this case the best solution is to unpivot your columns or use field parameters on the query editor if you select the Entity and do a unpivot others you should get a result similar to this:

Now you can use the attribute column has a slicer or has a column heard on your table visualziation:

[acg]

As the table is massive, is there a better solution than this one? https://community.powerbi.com/t5/Desktop/Filtering-by-column-headings/m-p/54080 ?

[v-rzhou-msft · Accepted solution]

Hi @acg ,

According to your statement, I think you want to show lines dynamicly by filter. One workaround is to UNPIVOT the columns that you need to make the column headers into one column and add it into Legend.

Or you can create a DimColumn header table and create a measure to achieve your goal.

Dim Column Header = {
    ("Full Time", 0),
    ("Non-Resident",  1),
    ("Other",  2),
    ("Part-Time",  3),
    ("Resident",4),
    ("Saisonal",  5)
}

Measure:

BS_Details_Last13Months =
VAR _1 =
    CALCULATE ( SUM ( 'Table'[Resident] ) )
VAR _2 =
    SUM ( 'Table'[Non-Resident] )
VAR _3 =
    SUM ( 'Table'[Saisonal] )
VAR _4 =
    SUM ( 'Table'[Full Time] )
VAR _5 =
    SUM ( 'Table'[Part-Time] )
VAR _6 =
    SUM ( 'Table'[Other] )
VAR _PARAMETER =
    VALUES ( 'Dim Column Header'[Value1] )
RETURN
    SWITCH (
        TRUE (),
        "Resident" IN _PARAMETER, _1,
        "Non-Resident" IN _PARAMETER, _2,
        "Saisonal" IN _PARAMETER, _3,
        "Full Time" IN _PARAMETER, _4,
        "Part-Time" IN _PARAMETER, _5,
        "Other" IN _PARAMETER, _6
    )

Result is as below.

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.