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
My data looks like below:
Person ID | Current Year | Past Years | Category | Contract Code | Person_Year_Points |
1 | 2024 | 2022 | M1 | A | 2 |
1 | 2024 | 2022 | M2 | A | 2 |
1 | 2024 | 2022 | M3 | A | 2 |
1 | 2024 | 2023 | M1 | B | 2.5 |
1 | 2024 | 2023 | M2 | B | 2.5 |
1 | 2024 | 2023 | M3 | B | 2.5 |
1 | 2024 | 2024 | M1 | C | 1 |
1 | 2024 | 2024 | M2 | C | 1 |
1 | 2024 | 2024 | M3 | C | 1 |
2 | 2024 | 2023 | M1 | A | 4 |
2 | 2024 | 2023 | M2 | A | 4 |
2 | 2024 | 2024 | M1 | A | 0 |
2 | 2024 | 2024 | M2 | A | 0 |
Person_Year_Points repeats itself for PersonID and Year Combinations. And that's because Category Code varies for each year. Since Person_Year_Points repeats itself, I need to get the distinct Person_Year_Points for each Person ID and Year Combination (excluding 2024) and sum them and add as a column to the dataset I am generating using DAX.
Person 1:
2022 value = 2
2023 value = 2.5
Sum (excluding 2024) = 4.5
Person 2:
2023 value = 4
Sum (excluding 2024) = 4
The output needs to look like below. The entire table visual is filtered for 2024. But the last column that Sums Point needs to be for prior years.
Person ID | 2024 Category | 2024 Contract Code | Sum of Person Year Points (exclude 2024) |
1 | M1 | B | 4.5 |
1 | M2 | B | 4.5 |
1 | M3 | B | 4.5 |
2 | M1 | A | 4 |
2 | M2 | A | 4 |
I need the measure to be simple as it is a large dataset. The measure will be defined inside a DAX query that will generate the above dataset. I am using a table visual to generate DAX query.
Solved! Go to Solution.
Hi @Hoping
Try creating a measure like this:
Person Year Points (exclude 2024) =
CALCULATE(
SUMX(
DISTINCT(
SELECTCOLUMNS(
FILTER(
'DataTable',
'DataTable'[Past Years] <> 2024
),
"Person ID", 'DataTable'[Person ID],
"YearPoints", 'DataTable'[Person_Year_Points]
)
),
[YearPoints]
),
ALLEXCEPT('DataTable', 'DataTable'[Person ID])
)
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hoping
Try creating a measure like this:
Person Year Points (exclude 2024) =
CALCULATE(
SUMX(
DISTINCT(
SELECTCOLUMNS(
FILTER(
'DataTable',
'DataTable'[Past Years] <> 2024
),
"Person ID", 'DataTable'[Person ID],
"YearPoints", 'DataTable'[Person_Year_Points]
)
),
[YearPoints]
),
ALLEXCEPT('DataTable', 'DataTable'[Person ID])
)
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Step 1=
find the points where current year is not equal to past years
Step 2= calculate the maximum value of contract code
Step 3= calculate the sum of person points for the past years which are not equal to current year
Final Visual
if this process is meeting your expectation then please accept the same as your solution
Hi @Rakesh1705 Thank You. Why are we doing a MAX Contract Code ? The output needs to hold 2024 Contract Code for the person, Person 1 it is C. Person 2 it is A. I am now adding a filter to the table visual to include Past Years = 2024. Only the Sum_Person_Points will be sum of past years excluding 2024. Other columns will be for 2024 only.
Hi @Hoping ,
There are multiple ways to achieve your desired output, and one of them is by writing a table DAX formula like the one below:
Table 2 =
FILTER(
SUMMARIZE(
'Table',
'Table'[Person ID],
'Table'[Category],
'Table'[Contract Code],
"Person Year Points (exclude 2024)",
CALCULATE(
SUM('Table'[Person_Year_Points]),
'Table'[Past Years] <> "2024"
)
),
[Person Year Points (exclude 2024)] <> BLANK()
)
The resulting output is shown below:
I am attaching an example pbix file.
Best regards,
Hi @DataNinja777 Thank You. I created a measure that includes the CALCULATE part of your code that does sum for past years <> 2024 but it never returns when I add it to the visual. It keeps circling until I remove the measure from the table. The dataset is medium sized but wide and has many columns pulled in from dimensions and all columns are for 2024 as the table visual is filtered to Past Year = 2024.
Is there another way to do it which is less compute intensive? SUMX over Staff ID and Year Combinations?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
28 | |
27 | |
20 | |
18 |