Remove duplicate values in a column based on Month and year with DAX

[rush]

Hi All

I have done a lookup to bring values in from another table which I have done so but it is displaying multiple values as there are multiple entries per month-year for a staff member

I am trying to remove duplicates or filter the lookup dax code to only bring one value per month-year.

My lookup dax code below:

Est. Monthly Cost = 

CALCULATE( 
    FIRSTNONBLANK(Billing_Summary[Est. Monthly Cost],TRUE()), 
    FILTER(Billing_Summary,Billing_Summary[StaffID_DateInt] = Billing_Hours_Detail[StaffID_DateInt] ) )

File - Example 

[v-stephen-msft]

Hi @rush ,

You can try to create an index column.

Then use the following measure

Measure = IF(MAX('Table1'[Index])=1,[Est. Monthly Cost])

Calculated columns can also be created as follows

Column = IF([Index]=1,[Est. Monthly Cost])

Example:

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rush]

@v-stephen-msft Unfortunately that did not work as I have numerous records for different staff for every month.

I think it would work if I can filter my calculated column to bring me the MAX value of Est. Monthly once for each month-year.

Please see DAX calculated column where I am trying to only bring one value per Staff per Month-Year but it is not working.

Max Monthly Cost per StaffID per Month-Year = 

CALCULATE( MAX(Billing_Hours_Detail[Est. Monthly Cost]) , KEEPFILTERS(Billing_Hours_Detail[Date_Int_YYYYMM01] ) , KEEPFILTERS(Billing_Hours_Detail[StaffID] ))

[v-stephen-msft]

Hi @rush ,

You could refer to this article to get a ranking group by staff name column.

Here's a simple example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdAxDsIwDAXQq6DMrRQ7seJ4Y2JFrFUHVLKhAqLcn7QkxVksD0/+Xx4GA4BoOnNJr096L3kD21vXIxwAxFozdrs5TlN6LulWkN8QCpBG5/t1nn8Gq3GCsBki5iYMqYaxgNNGhbm9kZecsaIYQ2gO+ZIVBRqhznCtEwRYm3/lWAmJs5qcHnnkrlS6ro8Zvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Phase = _t, #"Date start" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Phase", type text}, {"Date start", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date start", type datetime}}, "en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Code"}, {{"Column", each _, type table [Code=nullable number, Phase=nullable text, Date start=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Rank", each Table.AddIndexColumn([Column],"rank",1,1)),
    #"Expanded Rank" = Table.ExpandTableColumn(#"Added Custom", "Rank", {"Date start", "Phase", "rank"}, {"Rank.Date start", "Rank.Phase", "Rank.rank"})
in
    #"Expanded Rank"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rush]

@v-stephen-msft Thank you but this is not what I require.

I need to bring back one value per Staff per Month-Year.

[v-stephen-msft]

Hi @rush ,

Can you give all your sample data? From your dax, I guess you have two tables. Without sample data, it is difficult to meet your requirements by language description alone.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rush]

Hi @v-stephen-msft 

It is just one table containing multiple rows per year-month for different staff.

I am just trying to bring back one value from the Est. Monthly Cost column per staff per month-year. 

I have updated the link to a new excel file that contains sample data. The last column is what I am trying to achieve.

[v-stephen-msft · Accepted solution]

Hi @rush ,

1.Use group by

2.Add a custom column

3.Expand the column

4.Add a conditional column

5.The result

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.