Need immediate support for dax function

[xiumi_hou]

Dear all, 

I have attached my pdix file. 

I need the dax function by two aspects:

1.

First I want to create a bar graph, I want to pick the last session date's data(by id, which i have already filter one)

and show all the questions's score which is bigger than 2 in one bar chart 

Ex. 

In this example, for people 1, the last date should be 2018-08-01 and Q2/Q4/Q6/Q7's score on the bar chart since their score is bigger than 2

2.

Second, I still need a graph like this, I still want to pick the last session date's data and show the top3 questions score.

Ex. in the above question, the bar should show the highest three questions's score 

Can someone kindly build this dax function?

Below is the link to raw data:

https://1drv.ms/u/s!ArC4uvMBwvXve67V33EpYk5Fgpw?e=Hr2Yqp

https://1drv.ms/x/s!ArC4uvMBwvXveg8Z3LXLu2pwn6I?e=0yZhA8

Thanks!

[v-juanli-msft · Accepted solution]

Hi @xiumi_hou 

For question 2,

create a measure, then add this measure to the visual level filter of the bar chart.

rank = RANKX(ALLSELECTED(Sheet1),CALCULATE(SUM(Sheet1[Value])),,DESC,Dense)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xiumi_hou]

@v-juanli-msft Thank you so much!

[v-juanli-msft]

Hi @xiumi_hou 

For question1:

Based on the transformation in my previous post, create a measure, add this measure to the column/bar chart

Measure =
VAR maxdate =
    CALCULATE (
        MAX ( Sheet1[GAD.session_date] ),
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[ID] = MAX ( Sheet1[ID] ) )
    )
VAR adjusted_value =
    CALCULATE (
        SUM ( Sheet1[Value] ),
        FILTER ( Sheet1, Sheet1[GAD.session_date] = maxdate )
    )
RETURN
    IF ( adjusted_value > 2, adjusted_value )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

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"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xiumi_hou]

UP