Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct count returning two of the same value

Hi all, 

 

I posted this earlier, but deleted it after it was clear that it was a lot of text and data that would be better represented as an example PowerBI! 

 

pbix & Excel file 

 

Essentially, I am getting duplicate values returned in my contract values - so when viewing contracts for June 2020 I get:

  1. Smith / 2000 / 01-2019 / 06-2020
  2. Stevens / 2750 / 01-2020 / 01-2021

 

When in fact I should just get:

  1. Stevens / 2750 / 01-2020 / 01-2021

 

As the 'Smith' entry is two lines:

  1. Smith / 1005 / (blank) / 06-2020
  2. Smith / 2000 / 1005 / 05-2020

 

and the idea is that the code identifies the end date on the latest contract number, and uses that (and ignores the earlier date for the purposes of this exercise). 

 

Could someone provide me with some hints/guidance as to where I am going wrong with this?

 

Thanks!  

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ,

    Try it.

    ActiveContracts = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[main] ),
    FILTER (
        CALCULATETABLE ( VALUES ( 'Table'[Start Date] ), ALL ( 'Date' ) ),
        'Table'[Start Date] <= CALCULATE ( MAX ( 'Date'[Date] ) )
    ),
    FILTER (
        CALCULATETABLE ( VALUES ( 'Table'[LatestDate] ), ALL ( 'Date' ) ),
        'Table'[LatestDate] >= CALCULATE ( MIN ( 'Date'[Date] ) )
    ),
    ALLSELECTED( 'Date' )
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ,

    Try it.

    ActiveContracts = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[main] ),
    FILTER (
        CALCULATETABLE ( VALUES ( 'Table'[Start Date] ), ALL ( 'Date' ) ),
        'Table'[Start Date] <= CALCULATE ( MAX ( 'Date'[Date] ) )
    ),
    FILTER (
        CALCULATETABLE ( VALUES ( 'Table'[LatestDate] ), ALL ( 'Date' ) ),
        'Table'[LatestDate] >= CALCULATE ( MIN ( 'Date'[Date] ) )
    ),
    ALLSELECTED( 'Date' )
)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      v-yalanwu-msft 

       

      Excellent - thank you!

       

      So, the difference was ALL SELECTED as opposed to ALL. 

       

      One more query, sometimes there is a duplication in the records (as below) due to a related file elsewhere.  I had thought that the distinct count would have picked these out and made them into a single entry, but that's not the case?

       

      I've updated the file uploaded at the top with some new data, as below: 

       

      NameContractMain Contract NoStart DateEnd Date
      Davison3000 01/02/202231/12/2025
      Davison3000 01/02/202231/12/2025
      Davison1500 20/03/202031/12/2025
      Davison2250150020/03/202031/01/2022

       

      Any help would be greatly appreciated. Should I be creating a new table/column of unique values? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Apologies - to add to this - what I hadn't realised is that in using the 'month' filter I was filtering out start dates from outside this period; so although I can get the result I wanted by altering the filter - for the purposes of the graph I am still getting double reporting. 

         

        I'm not sure if the formula I am using is wrong, or should I be using a column instead of a measure to ensure that I can get distinct values? Obviously I can report on end dates and start dates, but it's ensuring that the values are counted for all the months in-between this. 

         

        Sorry for confusion, and happy to clarify if needed - particualrly if I'm overlooking something incredibly obvious... 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    I am very glad to hear that you have solved the problem. Could you  mark the  helpful reply as a solution to help others having the similar issue and close the case.

    Thank you.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.