Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i want to add a new column(status) to my table as below
The status column should be pass if type is all pass and fail if any fail by type.
table 1: (expected Status column)
Type status
fruit fail
veg pass
bean pass
table 2:
ID Type name status
2 fruit apple pass
3 veg eggplant pass
4 beans soya pass
5 fruit grapes fail
6 veg tomato pass
7 fruit banana pass
thank you
Solved! Go to Solution.
please try below DAX
status =
VAR failnum = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[type]=Table1[type]&&'Table'[status]="fail"))
return if(failnum>0,"Fail","pass")
Proud to be a Super User!
I think it always display pass because I only calculate fail times.
You can try below measure
status =
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="fail"))
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="pass"))
return if(failnum=0&&passnum=0,blank(),if(failnum>0,"Fail","pass"))
bean also shows blank because its name in your anothe table is beans and I also add a test type for testing.
Hope this is helpful.
Proud to be a Super User!
please try below DAX
status =
VAR failnum = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[type]=Table1[type]&&'Table'[status]="fail"))
return if(failnum>0,"Fail","pass")
Proud to be a Super User!
ran into issue. if there are no records. its displaying failed .can we have None instead failed for no records Type.
I think it always display pass because I only calculate fail times.
You can try below measure
status =
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="fail"))
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[status]="pass"))
return if(failnum=0&&passnum=0,blank(),if(failnum>0,"Fail","pass"))
bean also shows blank because its name in your anothe table is beans and I also add a test type for testing.
Hope this is helpful.
Proud to be a Super User!
i have another scenario that if value >90 display 'pass' and if value >80 dispaly 'average' and value less than 80 red' for type fruit from above table.
i tried below but didn't work
status2 =
VAR failnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value]>90)
VAR avgnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value] >80 && 'TEST'[value] <89 )
VAR passnum = CALCULATE(COUNTROWS('TEST'),FILTER('TEST','TEST'[type]='Table'[type]&&'TEST'[value]<80)
return if(failnum=0&&passnum=0,"Average",if(failnum>0,"Fail","pass"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |