Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So, I have a follow up question of sorts....I posted recently about how to do the following:
UserName isValid
A yes
A no
A yes
B yes
B yes
B yes
I would like a measure that would respond "NOT VALID" for A and "VALID" for B. Basically, any of the "isValid" = no, then "NOT VALID"....
And I solved it with this measure:
isQualified =
if (
COUNTROWS(
FILTER(
userQuals,
userQuals[IsQualified] = "FALSE"
)
) > 0,
"FALSE", "TRUE"
)
Now, I have a need to count the number of TRUE results from this measure, to come up with a total number of "qualified" users from the list...any way to accomplish this with another measure ?
Solved! Go to Solution.
If you want to count the number of rows for users who only have "yes" in the isValid column:
total rows of "is valid" users =
VAR UN = VALUES(isValid[User Name])
VAR valid = CALCULATETABLE(VALUES(isValid[User Name]);
FILTER(ALL(isValid);
isValid[isValid] = "no"))
Return
CALCULATE(COUNTROWS(isValid);
EXCEPT(UN; valid))
If what you want is to count the users who only have "yes" in the isValid column:
total number of users only "is valid" =
VAR UN = VALUES(isValid[User Name])
VAR valid = CALCULATETABLE(VALUES(isValid[User Name]);
FILTER(ALL(isValid);
isValid[isValid] = "no"))
Return
COUNTROWS(
EXCEPT(UN; valid))
Here is the example:
EDIT: Actually there is an easier way to calculate the number of users who only have "yes":
Number of users with only "yes" =
VAR withno = CALCULATE(DISTINCTCOUNT(isValid[User Name]);
FILTER(isValid;
isValid[isValid] = "no"))
VAR allUN = DISTINCTCOUNT(isValid[User Name])
RETURN
allUN - withno
Proud to be a Super User!
Paul on Linkedin.
If you want to count the number of rows for users who only have "yes" in the isValid column:
total rows of "is valid" users =
VAR UN = VALUES(isValid[User Name])
VAR valid = CALCULATETABLE(VALUES(isValid[User Name]);
FILTER(ALL(isValid);
isValid[isValid] = "no"))
Return
CALCULATE(COUNTROWS(isValid);
EXCEPT(UN; valid))
If what you want is to count the users who only have "yes" in the isValid column:
total number of users only "is valid" =
VAR UN = VALUES(isValid[User Name])
VAR valid = CALCULATETABLE(VALUES(isValid[User Name]);
FILTER(ALL(isValid);
isValid[isValid] = "no"))
Return
COUNTROWS(
EXCEPT(UN; valid))
Here is the example:
EDIT: Actually there is an easier way to calculate the number of users who only have "yes":
Number of users with only "yes" =
VAR withno = CALCULATE(DISTINCTCOUNT(isValid[User Name]);
FILTER(isValid;
isValid[isValid] = "no"))
VAR allUN = DISTINCTCOUNT(isValid[User Name])
RETURN
allUN - withno
Proud to be a Super User!
Paul on Linkedin.
Create a table by using summarize
https://docs.microsoft.com/en-us/dax/summarize-function-dax.
In this one, you can have columns like the sum of yes, the sum of nos , group by user name.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |