Keep single record from multiple records based on filter

[Anonymous]

Pretty simple i would think.

I want to keep only the names that have joined this year.

My dataset looks as:

Name Year

X  2019

Y  2020

Z  2019

Z  2020

A  2020

B  2019

B  2020

Desired

Name Year

Y  2020

A  2020

[camargos88]

Hi @Anonymous,

Check this file on Power Query editor: Download PBIX 

Ricardo

[Anonymous]

@camargos88 The attached doesnt give any direction besides a table with 4 rows.

[camargos88]

@Anonymous ,

Could you check the steps applied ?

I've create a table with the current year and joined with your table. It results on the values with 2020 year.

Doens't it work ?

Ricardo

[Anonymous]

@camargos88 i am looking for a DAX formula that will give me the desired result.

[camargos88]

@Anonymous ,

Try creating a new table like:

T =

VAR _year = YEAR(TODAY())

RETURN FILTER('Table'; 'Table'[Year] = _year)

Ricardo

[Anonymous]

@camargos88 If you look at the dataset above, A and Y are the only ones that have one record showing 2020. The others have either one record of 2019, or two records of 2019 and 2020. 

I am looking for a DAX formula that will be able to provide me with A and Y because those are the new names this year, because they do not contain a 2019 record.

Your solution will return the record that appeared in 2019 as well, if they have that record.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please try to create a measure to filter the  name that is the new name in the table, if it is new name ,return 1 , otherwise return 0:

Measure =

VAR a =

    YEAR ( TODAY () )

VAR b =

    CALCULATETABLE (

        VALUES ( 'Table'[Name] ),

        FILTER ( ALL ( 'Table' ), 'Table'[Year] <= a - 1 )

    )

RETURN

    IF ( MAX ( 'Table'[Name] ) IN b, 0, 1 )

Then create a table visual, drag year and name column in it , add the measure in visual level filter:

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETYicD9AZh5Ft3zrFDDoj5QB2Mja2BpnnVqNTyRSe3QXAw?e=1FeuNx

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

Best Regards,

Dedmon Dai

[Anonymous]

@v-deddai1-msft This helped but needed further assistance.
Year is currently in one column so [Company Name] will either have 1 or 2 records -- i want to create a column for each [Year] to show if the Type's changed from year to year. The difference in rows is the Type (e.g Premier, Select, Elite, etc.) Below is what i have and lower is my desired.

DAX for column [Tier for 2019] is same for 2020 except year change.

Below is my desired result:

[Ashish_Mathur]

Hi,

Do you want to know which Companies changed membership types from Year to year?  If yes, then share a sample dataset to work with and for that sample dataset show the expected result.

[camargos88]

@Anonymous ,

I didn't get you wanted to compare the previous values by name, give it a try:

T =

VAR _year = YEAR(TODAY())

VAR _tbl = SUMMARIZE(FILTER('Table'; 'Table'[Year] <> _year); 'Table'[Name])

RETURN FILTER('Table'; NOT 'Table'[Name] in (_tbl))

Ricardo