Forum Discussion
DAX Measure/Column Help
- 3 years ago
see attached
Thanks for respoding lbendlin . let me explain the scenarios -
there are 3 scenarios based on the year filter selction we make and year in Fact where each product belongs.
1.Year Selected silcer (from Year-Factor) > Fact Table Year (for each product) = here we need to consider factor to multiply price
2.Year Selected (Year-Factor) < Fact Table Year = here we need to consider factor2 to multiply price
3.Year Selected (Year-Factor) = Fact Table Year and Year Selected (Year-Factor)="None" = here we need to consider 1 to multiply price
ex. when we select 2021 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020 (Here Year Selected silcer (from Year-Factor) > Fact Table Year (for each product))
output= price * 2021factor = (10*1.04)
for product B which belongs to year 2021 (Here Year Selected (Year-Factor) = Fact Table Year)
output= price * 1 = (15*1)
for product C which belongs to year 2022 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2021factor2 = (20*0.98)
for product D which belongs to year 2023 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2022factor2 *2021factor2= (10*0.98*0.98)
ex. when we select 2022 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020
output= price * 2021factor*2022factor = (10*1.04*1.04)
for product B which belongs to year 2021
output= price * 2021factor = (15*1.04)
for product C which belongs to year 2022
output= price * 1 = (20*1)
for product D which belongs to year 2023
output= price *2022factor2= (10*0.98)
final measure output = sum of Product A output + Product B output + Product C output + Product D output
Year-Factor table-
Year Factor Factor2
2021 1.035 0.98
2022 1.035 0.98
2023 1.035 0.98
None 1 1
expected output -
| filter selection in slicer | ||||||
| Product | Year | Price | 2021 selected | 2022 selected | 2023 selected | None |
| A | 2020 | 10 | 10.35 | 10.71225 | 11.08717875 | 10 |
| B | 2021 | 15 | 15 | 15.525 | 16.068375 | 15 |
| C | 2022 | 20 | 19.6 | 20 | 20.7 | 20 |
| D | 2023 | 10 | 9.604 | 9.8 | 10 | 10 |