Distinct count returning two of the same value

[HawkB]

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!  

[v-yalanwu-msft]

Hi, @HawkB ;

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.

[v-yalanwu-msft · Accepted solution]

Hi, @HawkB ,

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.

[HawkB]

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

Name	Contract	Main Contract No	Start Date	End Date
Davison	3000		01/02/2022	31/12/2025
Davison	3000		01/02/2022	31/12/2025
Davison	1500		20/03/2020	31/12/2025
Davison	2250	1500	20/03/2020	31/01/2022

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

[HawkB]

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

[HawkB]

I think I've fixed this myself (whilst posting to myself!) but still with thanks for the initial help. 

In addition to the change above, I have added a new column 

Valid = IF('Table'[Contract]='Table'[LastestContract], 'Table'[LastestContract], 0)

Which I then set as a filter to remove any 0s, this removes duplicates and gives the correct result. 

The logic seems sound to me, but would welcome any feedback if I've done something clearly wrong in my thinking.