Forum Discussion

sravani9920's avatar
sravani9920
Frequent Visitor
2 years ago
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...
  • MFelix's avatar
    MFelix
    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