cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User IV
Super User IV

Re: Dax column calculation

OK, well here is your circular dependency when you use these together:

 

Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))

 

Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed]) ||  ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)

 

So, I don't understand that if the 2 things above are columns, why are you using VALUES? Are these measures or columns in a table?

 

Really, some SAMPLE data would be incredibly useful. Just make some up that emulates your actual data. Would only need 2 or 3 rows.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Dax column calculation

@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 

 

 

 

IdSiteCompliance DateDate PerformedTotal DueTotal Overdue% performed on time
1Amsterdam10/03/202009/03/202010100.00%
2Utrecht12/03/202010/03/202010100.00%
3Venlo12/03/202010/03/202010100.00%
4Amsterdam13/03/202015/03/2020110.00%
5Lellyan13/03/2020 11 ( over due because not performed)0.00%
6Milan17/03/202017/03/202010100.00%
7Rome 12/03/2020Not counted because there is no complaince date #VALUE!
8Spain13/03/202014/03/202010100.00%
9Utrecht13/03/202015/03/2020110.00%
10Amsterdam15/03/202015/03/202010100.00%
11Lisbon15/03/202015/03/202010100.00%
12Milan22/03/202022/03/2020Not counted because the date is in future #VALUE!
       
Total   10370.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
       
Highlighted
Super User IV
Super User IV

Re: Dax column calculation

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Dax column calculation

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.Audit.JPG

 

i did tried calculation to replace 1 by disntict count of ID's but no luck...:( still see the same numbers..

 

 

Highlighted
Super User IV
Super User IV

Re: Dax column calculation

Let me take a look, I need to understand what you are asking.

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Dax column calculation

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Dax column calculation

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: Dax column calculation

Greg, do you have a pbx file? Thanks

Highlighted
Super User IV
Super User IV

Re: Dax column calculation

@JCK2  - Yep!

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper II
Helper II

Re: Dax column calculation

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors