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
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!!
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 ) ) )
That's it! Thank you so much for your help
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?
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 ) ) )