Dynamic column name in a matrix based on a segmentation

[Anonymous]

Hello, 

I have a problem but I don't know if it is possible to solve it. 

Context:  I have a sales funnel comparing each step of the funnel X year vs X year, you can select the years you want to compare by the segmentation filter above... 

Right now I have put the name of the step + 1 (for YEAR 1) and step +2 (for YEAR 2), for example, -> Leads 1 | Leads 2, but I want to know if there is a possibility to put like a dynamic name in the matrix bases in the year selected in the segmentation above, for example  :

• If I select YEAR 1 = 2020 and YEAR 2 = 2022   -> Lead 2020 |  Lead 2022 (in the name of the column)

Leads_1 =
VAR a = CALCULATE([Leads],Leads[LEAD]=1, FILTER('Date','Date'[Year]=[Year1]),  USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))
RETURN IF(ISBLANK(a),0,a)

Leads_2 =
VAR a = CALCULATE([Leads],Leads[LEAD]=1,FILTER('Date','Date'[Year]=[Year2]), USERELATIONSHIP('Date'[Date],Leads[F_ORIGEN_LEAD]))
RETURN IF(ISBLANK(a),0,a)

Thank you in advance. 

Dulce Gámez

[Anonymous]

Thank you very much!!!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I've got an option that hopefully won't need much rework in your report.

Create a table which has the combinations of headers you want for the selection of your comparison in the slicer.

ColumnsName = 
UNION (
    DISTINCT ( SELECTCOLUMNS ( 'Date', "Names", "Leads_" & 'Date'[Year] ) ),
    { "Admitidos 1", "Admitidos 2" }
)

Then in your matrix put Names in the Columns.

Finally, you'll need to create a measure for using in the Values section of the matrix visual.  This measure makes use of the SWITCH function to show the relevant values for the column header.  You're essentially mapping your desired column header names to your existing Measures in this formula.

Measure = 
SWITCH (
    MAX ( ColumnsName[Names] ),
    "Leads_" & [Year 1], [Leads_1],
    "Leads_" & [Year 2], [Leads_2],
    "Admitidos 1", [Admitidos 1],
    "Admitidos 2", [Admitidos 2]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.