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...
Show and Tell
  • 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

MFelix's avatar
MFelix
Super User
2 years ago

Hi sravani9920 ,

 

Believe you can use one of this options:
Powern Query:
Making use of the current query

  • Uppercase the fail/pass (to avoid errors on next steps)

 

  • Group by Name and with MIN Pass/Fail Column

 

  • Merge the query with itself

 

  • Change on the Merge Step the first table by the last step befor the uppercase (in my example was the source step)

  • Expand the column status

 

Column using DAX:
Create the following column:

Status DAX = 
VAR temptable = FILTER(
		SUMMARIZE(
			ALL(Grades),
			Grades[Name],
			Grades[Pass/fail]
		),
		Grades[Name] = EARLIER(Grades[Name])
	)
	RETURN
		MINX(
			temptable,
			Grades[Pass/fail]
		)

 

Use a disconnected table and measure:

  • Create a table with the Pass/Fail values
  • Add the following measure:
Status per student = VAR temptable = SUMMARIZE(
			Grades,
			Grades[Name],
			"@status", MIN(Grades[Pass/fail])
		)
		RETURN
			COUNTROWS(FILTER(
				temptable,
				[@status] IN VALUES('Status'[Status])
			))

Pbix file attach

 

 

sravani9920's avatar
sravani9920
Frequent Visitor
2 years ago

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 ğŸ™‚

 

  • MFelix's avatar
    MFelix
    Super User
    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

    • sravani9920's avatar
      sravani9920
      Frequent Visitor
      2 years ago

      Thank You so much Dear, It works🙂