Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, please refer to this pbix for this question.
Background:
I have the following data in the 'Products' table:
Id | CreatedDate | Grade | ModifiedDate | NewGrade |
1 | 10 Nov 2020 | A | 10 Nov 2020 | B |
2 | 12 Nov 2020 | A | 16 Nov 2020 | C |
3 | 15 Nov 2020 | B | 19 Nov 2020 | C |
4 | 22 Nov 2020 | B | 30 Nov 2020 | A |
The values of [Grade] and [NewGrade] are always either "A", "B", or "C".
There is also a 'DateDim' table with a one-to-many relationship to 'Products'.[CreatedDate]. A date range slicer is created for 'DateDim'.[Date]:
The Problem:
I want to know how many products are in each grade within the selected date range. However, if the max date in the date range slicer is >= [ModifiedDate], it should be grouped by the value in [NewGrade] instead of [Grade].
For example, the expected result for a date range between 10/11/2020 and 17/11/2020 should be:
Grade | Count |
B | 2 |
C | 1 |
Expected results for a date range between 10/11/2020 and 22/11/2020 should be:
Grade | Count |
B | 2 |
C | 2 |
How can I achieve these results?
Solved! Go to Solution.
Hello, I managed to achieve what I needed by creating a measure for each grade like so:
_ProductCount_GradeA =
VAR slicerMaxDate = MAX(DateDim[Date])
RETURN
CALCULATE(
COUNT(Products[Id]),
FILTER(
ALL(Products[ModifiedDate], Products[Grade], Products[NewGrade]),
IF(Products[ModifiedDate] <= slicerMaxDate, [NewGrade], [Grade]) = "A"
)
) + 0
You may refer to this PBIX for my solution.
Thanks to everyone who took the time to consider my question. Have a nice day!
Hello, I managed to achieve what I needed by creating a measure for each grade like so:
_ProductCount_GradeA =
VAR slicerMaxDate = MAX(DateDim[Date])
RETURN
CALCULATE(
COUNT(Products[Id]),
FILTER(
ALL(Products[ModifiedDate], Products[Grade], Products[NewGrade]),
IF(Products[ModifiedDate] <= slicerMaxDate, [NewGrade], [Grade]) = "A"
)
) + 0
You may refer to this PBIX for my solution.
Thanks to everyone who took the time to consider my question. Have a nice day!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
84 | |
67 | |
63 | |
63 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
71 |