cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bstock Frequent Visitor
Frequent Visitor

How to count the result of a measure

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 ? 

1 ACCEPTED SOLUTION

Accepted Solutions
PaulDBrown New Contributor
New Contributor

Re: How to count the result of a measure

@bstock 

 

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:count valid users.JPG

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

 

 

View solution in original post

2 REPLIES 2
amitchandak Super Contributor
Super Contributor

Re: How to count the result of a measure

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.

PaulDBrown New Contributor
New Contributor

Re: How to count the result of a measure

@bstock 

 

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:count valid users.JPG

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

 

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)