Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 student fail in one test also status will be fail,
I need to present the data in PIE chart like number of test failed and passed.
Please any one help me on this,
Thank you in advance.
number
Name | test | Pass/fail | status |
sravani | test1 | Pass | fail |
sravani | test2 | Pass | fail |
sravani | test3 | fail | fail |
srinu | test1 | Pass | pass |
srinu | test2 | Pass | pass |
siri | test1 | Pass | pass |
siri | test2 | Pass | pass |
siri | test3 | Pass | pass |
mokshu | test1 | fail | fail |
mokshu | test2 | pass | fail |
mokshu | test3 | pass | fail |
mokshu | test4 | pass | fail |
Solved! Go to Solution.
Hi @sravani9920 ,
You need to add the class on your solution:
Power Query:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sravani9920 ,
Believe you can use one of this options:
Powern Query:
Making use of the current query
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:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHelli 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:
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank You so much Dear, It works🙂
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
Proud to be a Super User!
User | Count |
---|---|
99 | |
90 | |
85 | |
74 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |