Various count measures

[supergallagher25]

Hello,

I am trying to do various counts of measure. I have a sample table where I am trying to count if an ID has passed once, passed once or more, passed twice, passed twice or more. It seemed pretty easy to do at first but now I can see my numbers inflating. And I was wondering if somebody could help me figure this out. I am attaching the sample file below.

VariousCounts.pbix (23.6 KB)

Here is my table,

[Heather]

Looking at your source file, I’m confused by what exactly you are trying to return.

Example, I see ID 101 shows “Passed” three times - which do you want:

• each of those rows to return “Passed Twice or More”
• to see all possible values (passed once or more, passed twice or more, etc?)
• just a count of how many times the ID passed? (so “3”)

[Heather]

Well, I’m offline for dinner, not sure if I’m back this evening, so attached is your sample file with some ideas.

eDNA File - VariousCounts.pbix (25.6 KB)

[Harsh]

Hello @supergallagher25,

Thank You for posting your query onto the Forum.

Well firstly, I would like to point out that the condition that you’ve specified is contradictory here. “Passed Once or More” and “Passed Twice” are one and the same. How you can create two logics with the same set of conditions that are contradictory in nature. If a person is passing twice than it by default implicates that the person has already passed more than once.

And now, based on that, I’ve created the formulas based on the measure branching technique implying three set of conditions i.e. “Passed Once”, “Passed Twice” and “Passed More Than Twice”. Below are the formulas provided for the refernce alongwith the screenshot of the final result for the reference -

Passed = 
CALCULATE( COUNTROWS( 'Table' ) , 
    'Table'[Passed/Failed] = "Passed" )


Failed = 
CALCULATE( COUNTROWS( 'Table' ) , 
    'Table'[Passed/Failed] = "Failed" )


Passed Results = 
SWITCH( TRUE() , 
    [Passed] >= 1 && [Passed] < 2 , "Passed Once" , 
    [Passed] >= 2 && [Passed] < 3 , "Passed Twice" , 
    [Passed] > 2 , "Passed More Than Twice" ,
0 )


Failed Results = 
SWITCH( TRUE() , 
    [Failed] >= 1 && [Failed] < 2 , "Failed Once" , 
    [Failed] >= 2 && [Failed] < 3 , "Failed Twice" , 
    [Failed] > 2 , "Failed More Than Twice" , 
BLANK() )

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your reqiurements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

VariousCounts - Harsh.pbix (25.4 KB)

[supergallagher25]

Harsh,

Thank you so much for such a prompt response, however, I am trying create individual measures. I tried the following for passed once, but I do not exactly know what I might be doing incorrectly here. I am attaching a screenshot along with the formula below.

Passed only once =

var test = SWITCH( TRUE() ,
[Passed] >= 1 && [Passed] < 2 , DISTINCTCOUNT(‘Table’[ID]) ,
0 )

return
SUMX(SUMMARIZE(‘Table’,‘Table’[ID],“Total Passed”,test),[Total Passed])

Please let me know when you see this.

Thanks.

[Harsh]

Hello @supergallagher25,

Could you please specify the following -

1. If want the individual measures than the use of “SWITCH( TRUE() )” function.

2. Why you’re trying to use the “DISTINCTCOUNT()” function? Since the primary formula was created based on “COUNTROWS()” function.

3. And the file that you had provided initially in that Subjects are not available.

Please provide the revised file alongwith the proper details/description so that we don’t have to work repetitively.

Thanks and Warm Regards,
Harsh

[supergallagher25]

I did distinct count as an example, I guess I am trying to get a count of users. I apologize, I created the subjects column later for showing the table. I am attaching the file below with the measures on the table.

VariousCounts (2).pbix (27.1 KB)

[Harsh]

Hello @supergallagher25,

So the individual formulas will be the extension of the formulas that I’ve provided above. To calculate the “Passed Once” results, below is the formula provided for the reference -

Passed Once - Harsh = 
IF( [Passed Results] = "Passed Once" , 1 , BLANK() ) 

But as you can see in the above screenshot, the measure doesn’t provide the results of “Grand Total” so now to calculate the “Grand Total” results of measure “Passed Once”. Below is the formula provided for the reference -

Passed Once - Harsh - Total = 
SUMX(
    SUMMARIZE(
        'Table' , 
        'Table'[ID] , 
        'Table'[Subject] , 
        "Totals" , 
        [Passed Once - Harsh] ) , 
    [Totals]
)

Now, follow this same process for “Passed Twice”, “Passed More Than Twice” as well as for the Failed side, if required.

Finally, you’ll be able to achieve the results that you’ve been trying to showcase. Below is the screenshot of the final result provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Important Note:

1. Since no person has passed/failed more than twice the results are shown as “BLANKS”. If there’s a data in your actual file where person has passed/failed more than twice than the formula will calculate the results and the figures will be showcased automatically.

2. In the file, I’ve created the groups of the measures and for “Passed” and “Failed” categories alongwith the numbering provided to each measure so that you can understand how the measure flows. Below is the screenshot provided for the reference -

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

VariousCounts - Harsh v2.pbix (33.2 KB)

[supergallagher25]

Thank you, this helped a lot.

[Harsh]

Hello @supergallagher25,

You’re Welcome.

I’m glad that I was able to assit you.

Thanks and Warm Regards,
Harsh