Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Keep single record from multiple records based on filter

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

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

 

Untitled picture.png

 

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

View solution in original post

10 REPLIES 10
camargos88
Community Champion
Community Champion

Hi @Anonymous,

 

Check this file on Power Query editor: Download PBIX 

 

 

Ricardo



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

Proud to be a Super User!



Anonymous
Not applicable

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

@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



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

Proud to be a Super User!



Anonymous
Not applicable

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

@Anonymous ,

 

Try creating a new table like:

 

T =
VAR _year = YEAR(TODAY())
RETURN FILTER('Table'; 'Table'[Year] = _year)
 
Ricardo


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

Proud to be a Super User!



Anonymous
Not applicable

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

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:

 

Untitled picture.png

 

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
Not applicable

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

Capture1.JPG

Below is my desired result:
Capture.JPG

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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


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

Proud to be a Super User!



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.