COUNTIF case in A but not in B

[setis]

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!

[AlB · Accepted solution]

@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
        )
    )
)

[AlB]

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

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

[setis]

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. 

[AlB]

@setis 

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

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]

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

[AlB]

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]

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. 

[AlB]

@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]

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:

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

[setis]

I think that I found where the problem is. In the table [Financial_combined] the same CaseID can be several times and sometimes there are lines cancelling themselves. I need to tweak the measure so it still counts the nr of distinc ID, but for this particular one (Payment&NoFee) when the SUM of the payments is >0 and the SUM of the Fees is 0 or blank

[setis]

Dear @AlB ,

The measure that I'm using following your model is 

However, as you can see here it looks like there is an error:

The measure is counting this row even when the amount in "TotalAmount DKK" is "0"

When I add the posting date to the table I can see that there are 2 lines with this ID and one of them has a "TotalAmount DKK" >0

So as you can see the measure actually works counting the amount>0 but I need to define that it has to SUM the TotalAmount DKK for the unique IDs and count it if the result is >0. 

I hope that this make sense. 

Thanks again!! 

[AlB · Accepted solution]

@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
        )
    )
)

[setis]

That's it! Thank you so much for your help 🙂

[setis]

Dear @AlB ,

I told you that everything worked but I found an issue. One of the measures that I need is: Fees & Payments; this is CaseID's where payments sum >0 and Fees sum >0.

It's important to mention that Fees can be blank or >0 whereas Payments can be >=0, <0 or blank. 

The measure that I'm using is: 

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

There is a small difference in the nr of cases when I filter the table with visual filters (Amount>0 and fee>0) and when I do it with the measure above. 

When I do it with the measure it comes 7 cases with the fees blank. I checked into the cases and there are no fees in those. They still show up with this measure. Do you have any idea why it could be?

[AlB · Accepted solution]

@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
        )
    )
)

[setis]

@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: