Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community!
Here is an issue I keep thinking about without finding the solution...
I have 2 tables :
product
ProductCode | FirstRelease | EndOfProduct |
PC_01 | 02/01/1990 | |
PC_02 | 01/06/2017 | |
PC_03 | 01/06/2018 | |
PC_04 | 01/06/2019 | |
PC_05 | 01/06/2020 | |
PC_06 | 01/01/2021 | |
PC_07 | 02/01/1990 | |
PC_08 | 02/01/1990 | 01/03/2018 |
PC_09 | 02/01/1990 | 01/03/2019 |
PC_10 | 02/01/1990 | 01/03/2020 |
PC_11 | 02/01/1990 | 01/01/2021 |
and years:
Years |
2018 |
2019 |
2020 |
2021 |
And I need to calculate how many products were live for each year (with a fixed date wihtin the year, for example Jan 1st) : for each row in Year table, calculate distinctcount(ProductCode) from Product table where FirstRelease <= 01/01/Year and (EndOfProduct is null or EndOfProduct > 01/01/Year).
I manage to do that in PowerQuery but I wonder if it's possible in DAX, as my product table has many many many rows and multiplying them by the number of years to evaluate is not the best. IRL, my ProductTable also have other columns which are used as filters in the report, that's why the counting needs to be dynamic.
Any help appreciated !
Solved! Go to Solution.
Try this measure. No relationship exists between the two tables.
Distinct Product Count =
VAR vYear =
MAX ( Years[Year] )
VAR vStartDate =
DATE ( vYear, 1, 1 )
VAR vProductRows =
FILTER (
Products,
vStartDate >= Products[FirstRelease]
&& (
vStartDate <= Products[EndOfProduct]
|| ISBLANK ( Products[EndOfProduct] )
)
)
VAR vResult =
CALCULATE ( DISTINCTCOUNT ( Products[ProductCode] ), vProductRows )
RETURN
vResult
Proud to be a Super User!
Thank you so much ! It works, and I understand the measure's code, so I've improved my DAX skill 🙂
Glad to hear that worked, and that your DAX skills improved. 🙂
Proud to be a Super User!
Try this measure. No relationship exists between the two tables.
Distinct Product Count =
VAR vYear =
MAX ( Years[Year] )
VAR vStartDate =
DATE ( vYear, 1, 1 )
VAR vProductRows =
FILTER (
Products,
vStartDate >= Products[FirstRelease]
&& (
vStartDate <= Products[EndOfProduct]
|| ISBLANK ( Products[EndOfProduct] )
)
)
VAR vResult =
CALCULATE ( DISTINCTCOUNT ( Products[ProductCode] ), vProductRows )
RETURN
vResult
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |