Forum Discussion
Dax column calculation
JCK2 - Yep!
Greg_Deckler , I've created a sample data set, also tried to explain as i can!
Hope its clear, please let me know if you need more info! Thanks a ton 🙂
Sorry here is the link to https://www.dropbox.com/sh/w1cxxiijhn7060u/AACtywXqUK00HuAKWRP2vstYa?dl=0
| Id | Site | Compliance Date | Date Performed | Total Due | Total Overdue | % performed on time |
| 1 | Amsterdam | 10/03/2020 | 09/03/2020 | 1 | 0 | 100.00% |
| 2 | Utrecht | 12/03/2020 | 10/03/2020 | 1 | 0 | 100.00% |
| 3 | Venlo | 12/03/2020 | 10/03/2020 | 1 | 0 | 100.00% |
| 4 | Amsterdam | 13/03/2020 | 15/03/2020 | 1 | 1 | 0.00% |
| 5 | Lellyan | 13/03/2020 | 1 | 1 ( over due because not performed) | 0.00% | |
| 6 | Milan | 17/03/2020 | 17/03/2020 | 1 | 0 | 100.00% |
| 7 | Rome | 12/03/2020 | Not counted because there is no complaince date | #VALUE! | ||
| 8 | Spain | 13/03/2020 | 14/03/2020 | 1 | 0 | 100.00% |
| 9 | Utrecht | 13/03/2020 | 15/03/2020 | 1 | 1 | 0.00% |
| 10 | Amsterdam | 15/03/2020 | 15/03/2020 | 1 | 0 | 100.00% |
| 11 | Lisbon | 15/03/2020 | 15/03/2020 | 1 | 0 | 100.00% |
| 12 | Milan | 22/03/2020 | 22/03/2020 | Not counted because the date is in future | #VALUE! | |
| Total | 10 | 3 | 70.00% | |||
| Total Due: Countdistint of id where compliance date is non blank and less than today (ID is unique and there can be multiple audits on same date), so need to take id into consideration for total due | ||||||
OK, I did it with these two columns:
Total Due 1 = IF(ISBLANK([Compliance Date]) || [Compliance Date] > TODAY(),BLANK(),1)
Total Overdue 1 =
SWITCH(TRUE(),
ISBLANK([Compliance Date]),BLANK(),
ISBLANK([Date Performed]) && [Compliance Date] < TODAY(),1,
[Date Performed] > [Compliance Date],1,
[Compliance Date]>TODAY(),BLANK(),
0
)
And this measure:
Measure =
DIVIDE(COUNTROWS(FILTER('Table',NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)),SUM('Table'[Total Due 1]))PBIX is attached. I think you have an error in your data for Spain. Spain looks like it is overdue.
Greg_Deckler Thanks a lot for your effort and patience, it worked :)!
I started using Powerbi from last week, always have been using Alteryx and Tableau...this was my first challange!!
Appreciate your help in onboarding and making me love PowerBi!!
Look forward to keep learning more things from you Greg!! You are a True Guru :)! Thanks once again!
- Let me take a look, I need to understand what you are asking.
Apologies, maybe this:
Measure 2 = DIVIDE( COUNTROWS(FILTER(SUMMARIZE('Table',[Id],"__Count",IF(NOT(ISBLANK(MAX('Table'[Total Overdue 1]))) && MAX([Total Overdue 1]) = 0,1,0)),[__Count]=1)), SUM('Table'[Total Due 1]) )- Anytime!
Thanks a lot Greg!! Yes you are right...Spain should be over due....!
For the total due, instead of counting it as 1, if we use distinct count of the id's, we are there...currently this is the result..because 1 audit can be done at two places with same id and we want to calculate it as 1.
i did tried calculation to replace 1 by disntict count of ID's but no luck...:( still see the same numbers..
JCK2 - are you looking for this?
Measure 2 = DIVIDE( COUNTROWS(FILTER(FILTER('Table',DISTINCT('Table'[Id])),NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)), SUM('Table'[Total Due 1]) )Greg, do you have a pbx file? Thanks
JCK2 - Yep!