Forum Discussion
sravani9920
2 years agoFrequent Visitor
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...
- 2 years ago
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
PhilipTreacy
2 years agoSuper User
Hi sravani9920
Download PBIX file with the examples below
This measure will give you the Status column as shown in your example
Status = IF(CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name]) && 'DataTable'[Pass/fail] = "fail")), "Fail", "Pass")
These measures will give you the number of tests passed and failed by each person
Tests Passed = CALCULATE(COUNTROWS('DataTable'), FILTER(('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name]) && 'DataTable'[Pass/fail] = "pass"))
Tests Failed = CALCULATE(COUNTROWS('DataTable'), FILTER(('DataTable'), 'DataTable'[Name] = SELECTEDVALUE('DataTable'[Name]) && 'DataTable'[Pass/fail] = "fail"))
I don't think a pie chart is the best visual for this data, how exactly do you expect it to look? Segments are coloured by the candidate, so both pased and failed tests are the same colour for each person.
A column chart would be better
Regards
Phil