I hope that you can help me with this.
I have 3 tables with the following relationships:
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 ) )
Solved! Go to Solution.
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 ) ) )
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 ) ) )
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 ) )
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.
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]" ? )
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:
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
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.
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.
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 ) ) )
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.