Need immediate support for dax function

Hi xiumi_hou 

First we need to transform your table.

In Edit queries, unpivot columns for "q1"~"q8",

Then split column "Attribute" by delimiter ".",

Rename "Attribute.2" with "GAD",

Then select "GAD" column, Add column->extract last character, rename the column as "question index".

Close&&apply, return to Data model view.

You can replace the connection path with yours in my pbix file so to see details of each step.

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\Question2.xlsx(replace it with your file path)"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"GAD.session_date", type date}, {"GAD.q1", Int64.Type}, {"GAD.q2", Int64.Type}, {"GAD.q3", Int64.Type}, {"GAD.q4", Int64.Type}, {"GAD.q5", Int64.Type}, {"GAD.q6", Int64.Type}, {"GAD.q7", Int64.Type}, {"GAD.q8", Int64.Type}, {"GAD.total", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "GAD.session_date", "GAD.total"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.2", "GAD"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}),
    #"Inserted Last Characters" = Table.AddColumn(#"Removed Columns", "Last Characters", each Text.End([GAD], 1), type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Last Characters",{{"Last Characters", "question index"}})
in
    #"Renamed Columns1"