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

Re: COUNTIF case in A but not in B

 

 

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

 

 

error2.PNG

 

 

 

 

 

 

Highlighted
setis Member
Member

Re: COUNTIF case in A but not in B

Dear @AlB ,

The measure that I'm using following your model is 

E1.PNG

 

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

E6.PNG

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

 

e7.PNG

 

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!! 

 

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
        )
    )
)
setis Member
Member

Re: COUNTIF case in A but not in B

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

setis Member
Member

Re: COUNTIF case in A but not in B

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?

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

     

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 77 members 1,133 guests
Please welcome our newest community members: