Forum Discussion
Solved
DAX needed
Hello Guys, I have provided the sample data for you, Here status column is the Result column for my requirement, student should be passed in all the tests then only status should be pass, If any stu...
Hi sravani9920 ,
You need to add the class on your solution:
Power Query:- Class must be added to the Group By and Merged queries steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5KLEvMy1TSUSpJLS4xBNIBicXFQMpQKVYHXdoIv7QxkE5LzMxBks7MK8U02hhD0ghDMrMIt5sQcpgOQsgZo8vl5mcXZyA7B8WtKLIgcwtw6jXGK2uCLosziI3wBzFQOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"test " = _t, #"Pass/fail" = _t, Class = _t]), #"Uppercased Text" = Table.TransformColumns(Source,{{"Pass/fail", Text.Upper, type text}}), #"Grouped Rows" = Table.Group(#"Uppercased Text", {"Name", "Class"}, {{"Status", each List.Min([#"Pass/fail"]), type text}}), #"Merged Queries" = Table.NestedJoin(Source, {"Name","Class"}, #"Grouped Rows", {"Name", "Class"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Status"}, {"Status"}) in #"Expanded Grouped Rows"Calculated column:
Status DAX = VAR temptable = FILTER( SUMMARIZE( ALL(Grades), Grades[Name], Grades[Class], Grades[Pass/fail] ), Grades[Name] = EARLIER(Grades[Name]) && Grades[Class] = EARLIER(Grades[Class]) ) RETURN MINX( temptable, Grades[Pass/fail] )Dax measure:
Status per student = VAR temptable = SUMMARIZE( Grades, Grades[Name], Grades[Class], "@status", MIN(Grades[Pass/fail]) ) RETURN COUNTROWS(FILTER( temptable, [@status] IN VALUES('Status'[Status]) ))File attach
Helli MFelix,
It worked well for me but need to extend the DAX calcultaed formula as sravani student in class 2 then the status should be Pass for class 2 as all the tests passed in class2 and same person is failed in class 1 so status for should be failed,
Please help me out by extending the Dax calcultaed formula,
Thank you 🙂
Hi sravani9920 ,
You need to add the class on your solution:
Power Query:
- Class must be added to the Group By and Merged queries steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5KLEvMy1TSUSpJLS4xBNIBicXFQMpQKVYHXdoIv7QxkE5LzMxBks7MK8U02hhD0ghDMrMIt5sQcpgOQsgZo8vl5mcXZyA7B8WtKLIgcwtw6jXGK2uCLosziI3wBzFQOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"test " = _t, #"Pass/fail" = _t, Class = _t]),
#"Uppercased Text" = Table.TransformColumns(Source,{{"Pass/fail", Text.Upper, type text}}),
#"Grouped Rows" = Table.Group(#"Uppercased Text", {"Name", "Class"}, {{"Status", each List.Min([#"Pass/fail"]), type text}}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name","Class"}, #"Grouped Rows", {"Name", "Class"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Status"}, {"Status"})
in
#"Expanded Grouped Rows"
Calculated column:
Status DAX =
VAR temptable = FILTER(
SUMMARIZE(
ALL(Grades),
Grades[Name],
Grades[Class],
Grades[Pass/fail]
),
Grades[Name] = EARLIER(Grades[Name]) &&
Grades[Class] = EARLIER(Grades[Class])
)
RETURN
MINX(
temptable,
Grades[Pass/fail]
)
Dax measure:
Status per student = VAR temptable = SUMMARIZE(
Grades,
Grades[Name],
Grades[Class],
"@status", MIN(Grades[Pass/fail])
)
RETURN
COUNTROWS(FILTER(
temptable,
[@status] IN VALUES('Status'[Status])
))
File attach
Thank You so much Dear, It works🙂