cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
setis Member
Member

COUNTIF case in A but not in B

Hi all,

 

I hope that you can help me with this. 

 

I have 3 tables with the following relationships:

 

[Fees]*--->1[Cases]1<---*[Financial] 

 

I would like to have a measure to calculate the number of cases that are in [Fees] but not in [Financial]

 

I have a card with the following measure:

 

NrCasesFee&NOPayment = CALCULATE (
            DISTINCTCOUNT(Fees[CaseID]);
            FILTER (
                ALL (Fees[CaseID]);
                CALCULATE(SUM(Financial[Amount Paid]))=0
            )
)
My issue is that when I select a case in the table [Fees] and the case is not in [Financial], the measure works (showing "1")
 
However, when nothing is selected, instead of showing the Nr of cases in [Fees] that are not in [Financial], it shows "Blank"
 
What am I doing wrong?
 
Thanks in advance!
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: COUNTIF case in A but not in B

@setis 

I see

You have to modify the filtering in the CALCULATETABLE then. Right now we are just filtering for amount > 0 and that is why the row > 0 is taken into account. You want SUM(amount) > 0 as we do working out the second listing.

 

 

NrCasesPayment&NOFee =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Financial[CaseID] );
            CALCULATE ( SUM ( Financial[Amount Paid] ) ) > 0
        );
        FILTER (
            DISTINCT ( Cases[CaseID] );
            CALCULATE ( SUM ( Fees[Amount Paid] ) ) > 0
        )
    )
)

 

which if I'm not mistaken should be equivalent to this with your set-up:

 

NrCasesPayment&NOFee =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Cases[CaseID] );
            CALCULATE ( SUM ( Financial[Amount Paid] ) ) > 0
        );
        FILTER (
            DISTINCT ( Cases[CaseID] );
            CALCULATE ( SUM ( Fees[Amount Paid] ) ) > 0
        )
    )
)

 and to this:

 

NrCasesPayment&NOFee =
COUNTROWS (
    EXCEPT (
        FILTER (
            DISTINCT ( Financial[CaseID] );
            CALCULATE ( SUM ( Financial[Amount Paid] ) ) > 0
        );
        FILTER (
            DISTINCT ( Fees[CaseID] );
            CALCULATE ( SUM ( Fees[Amount Paid] ) ) > 0
        )
    )
)
Super User
Super User

Re: COUNTIF case in A but not in B

@setis 

 

For this to work well, all IDs with payment in 'Financials' would need to be in 'Fees' (either with or without payment). That seems not to be the case.  The missing ones will not be removed by the EXCEPT. 

A safer, less restrictive option is to go with INTERSECT:

 

NrCases Payments&Fees2 = 
COUNTROWS(
    INTERSECT(
        FILTER(
            DISTINCT (Financial_Combined[Job No.]);
            CALCULATE ( SUM (Financial_Combined[Amount Paid DKK]))>0
        );
        FILTER (
            DISTINCT (Fees[CaseID]);
            CALCULATE (SUM (Fees[FeeAmountDKK])) > 0
        )
    )
)

     

15 REPLIES 15
Super User
Super User

Re: COUNTIF case in A but not in B

Hi @setis 

 

I'm not sure I've understood correctly but try this. If it works we can delve into why your code was failing...  if you want to Smiley Happy

 

NrCasesFee&NOPayment = CALCULATE (
            DISTINCTCOUNT(Fees[CaseID]);
            FILTER (
                ALL (Cases[CaseID]);
                CALCULATE(SUM(Financial[Amount Paid]))=0
            )
)

 

setis Member
Member

Re: COUNTIF case in A but not in B

Dear @AlB

 

Thank you so much for your quick answer. I think it works. I took a small sample and did a countif in excel with a result of 126 and i get 127 in PBI. I'll take a look again but I'm positive that the measure is correct. 

 

 

Super User
Super User

Re: COUNTIF case in A but not in B

@setis 

 

Don't be so sure until you see it working properly Smiley Very Happy

If it doesn't work  share the pbix and/or explain exactly what type of filtering you are doing since that might play a role (you talk about selecting "a case in the table [Fees]" ? )

setis Member
Member

Re: COUNTIF case in A but not in B

@AlB ,

 

It's weird, it keeps giving me the correct result +1 in the PBI file comparing it with a COUNTIF in excel. 

 

What I told you about selecting one case in FEES is this for testing purposes:

 

T1.PNGT2.PNG

 

 

 

setis Member
Member

Re: COUNTIF case in A but not in B

Dear @AlB 

 

I manage to create a file reproducing the real one. I'm not sure that the measures I created are correct. 

 

Could you please take a look at it here? https://drive.google.com/file/d/1ptapFstvQutCHbTA12AHDmXvAsMfRJxc/view?usp=sharing

 

Super User
Super User

Re: COUNTIF case in A but not in B

Dear @setis 

 

A few issues:

 

1. You didn't tell me about the Date table. The dates in your Cases table include time so you won't be able to filter on that column with a Date table that does not include time. You'd have to create an additional column in Cases with only date (i.e., time 00:00:00)

2. The measures look ok but I won't be able to determine whether they are correct until I know exactly what every measure is supposed to do. What is "cases with no fees", for instance? Does that case have a fee when it has an entry in the Fees table? Or only if it's on that table but has a non-blank amount, or non-zero? And so on...

3. What kind of filtering do you ultimately want to do? With dates? with  something else? That's important too.

 

         

setis Member
Member

Re: COUNTIF case in A but not in B

Hi @AlB 

 

Apologies for not giving you this information before. 

 

1.-In my real report the Date in the calender table are connected to a column to cases with Date only. It's my mistake that I didn't reproduce this in the sample file. 

 

2.- I consider a case present in either table when the amount is >0. If there's no entry or the value is blank or 0, there's no case in Fees or Payments. 

 

*Nr. Cases: Case count from [Cases]

*NrCasesFee&NoPayment: Nr of Cases ID not in the table [Payments] or with Amount 0 or blank and in the table [Fees] with Amount >0

*NrCases Fee&Payment: Nr of Cases in table [Fees] with Amount>0 and in [Payments] with Amount >0

*NrCasesNOPayment&NOFee: Cases with Amounts 0 or blank in both tables

*NrCasesPayment&NOFee: Nr of Cases ID  in the table [Payments] with amount >0 And not in [Fees] or in with Amount 0 or blank

 

3. the filter will be on the "created on" from Cases but a column without the hours. 

 

Thank you so much for helping me with this. I really appreciate it. 

 

Super User
Super User

Re: COUNTIF case in A but not in B

@setis 

 

There are many case IDs in both 'FEES' and 'Financials' that do not appear in 'Cases'. Why is this? That would be required for the approach you are using to work. Otherwise, you would have to go down the path of INTERSECT or other set functions and ignore the 'Cases' table in your measures.  

 

If 'Cases' has all case IDs, which would seem reasonable, you'd also have to modify your code so as to include only IDs in 'FEES' that have 'amount' greater than zero. I haven't tested it: 

 

NrCasesFee&NOPayment =
COUNTROWS (
    EXCEPT (
        CALCULATETABLE ( DISTINCT ( Fees[CaseID] ); Fees[Amount] > 0 );
        FILTER (
            DISTINCT ( Cases[CaseID] );
            CALCULATE ( SUM ( Financial[Amount Paid] ) ) > 0
        )
    )
)

   

setis Member
Member

Re: COUNTIF case in A but not in B

Dear @AlB ,

 

This seems to work in most scenarios.  You are right, the Cases table contains all the IDs. I made a mistake not including them in the example pbix that I sent for this example. 

 

It's a great learning to see how you are using the EXCEPT and CALCULATETABLE functions here and I'll definitely will look for more documentation to truly understand how they are working. 

 

There is some error in one of my measures using your model that I can't really understand. Please see here:

 

error.PNG

 

I don't understand why these negative amounts and zeros are showing up. 

 

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 262 members 2,910 guests
Please welcome our newest community members: