Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bstock
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
PaulDBrown
Community Champion
Community Champion

@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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

@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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.