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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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