COUNT Distinct Column A if SUM of Column B = 0

[Anonymous]

Hello,

I have the following dataset:

Status	Transaction Rules	Total # of Complaints
Active	Rule Alpha	107
Active	Rule Bravo	0
Active	Rule Bravo	0
Active	Rule Charlie	6
Active	Rule Charlie	0
Active	Rule Delta	13
Active	Rule Delta	64
Active	Rule Delta	12
Active	Rule Echo	0
Inactive	Rule Foxtrot	3
Inactive	Rule Foxtrot	0

I'm trying to create a Measure I can throw into a Card that will show the following:

• No. of Active Unique "Transaction Rules" with 0 Complaints.
  • So as an example, for the above - this would be 2 Rules (Bravo and Echo)
  • I'm thinking that I need to create a Count Distinct of each of the Rule's, Filtered for "Active" and SUM within the Filtered Rule = 0.
  • Unsure how to create the above Measure to meet the above criteria?
• Conversely, the No. of Active "Transaction Rules" with at-least 1 Complaint
  • So, again for above - this would be 3 (Alpha, Charlie and Delta)
  • This would be Count Distinct again, and then filtered out for Inactive transaction rules. And then sum the no. of complaints, so if sum<>0 then it is counted.

Hopeful to see what people come up with. I have the following, but it doesn't seem to work: 

Measure 1 =

CALCULATE(

    COUNTROWS(DISTINCT('Table1'[Transaction Rules])),FILTER('Table1',SUM([Tot_Complaints]) = 0,[Status] = "Active)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Measures

count_ac&0 =
VAR s =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        'Table'[Transaction Rules],
        "sum", SUM ( 'Table'[Total # of Complaints] )
    )
RETURN
    COUNTROWS ( FILTER ( s, [sum] = 0 ) )


count&in>0 =
VAR s =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        'Table'[Transaction Rules],
        "sum", SUM ( 'Table'[Total # of Complaints] )
    )
RETURN
    COUNTROWS ( FILTER ( s, [sum] > 0 ) )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try something like

if(SUM('Table1'[Tot_Complaints]) = 0,DISTINCT('Table1'[Transaction Rules]),SUM('Table1'[Tot_Complaints] ))

or

if(isblank(SUM('Table1'[Tot_Complaints])) || SUM('Table1'[Tot_Complaints]) = 0,DISTINCT('Table1'[Transaction Rules]),SUM('Table1'[Tot_Complaints] ))

[Anonymous]

Hi @amitchandak ,

where do I put in a Filter for the Status?

Alternatively, I tried something like this but no luck:

Measure:

CALCULATE(

    DISTINCTCOUNT('Table1'[Transaction Rules]),

    filter('Table1','Table1'[Status] = "Active" && 'Table1'[Tot_Complaints] <> 0)

)