March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
@camargos88 The attached doesnt give any direction besides a table with 4 rows.
@camargos88 i am looking for a DAX formula that will give me the desired result.
@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:
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
@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:
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.
@Anonymous ,
I didn't get you wanted to compare the previous values by name, give it a try:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |