March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Register Now- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX Measure/Column Help

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

DAX Measure/Column Help

05-05-2023
08:24 AM

Hi All, Need a help to create DAX measure/column.

There are 2 tables, below is the sample data**1.Year-Factor**

which has Factor for each year

Year Factor

2021 1.035

2022 1.035

2023 1.035

None 1

**2.Fact**

there will be muliple products, multiple years and price

Product Year Price

A 2020 10

B 2021 15

C 2022 20

Requirement -

there will be Year slicer in the page from Year-Factor table and user will be choosing the year

ex. when they select 2022

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 * 2022factor

final measure output - sum of Product A output + Product B output + Product C output

ex. when they select 2023

expected output -

for product A which belongs to year 2020

output= price * 2021factor * 2022factor * 2023factor(10*1.04*1.04*1.04)

for product B which belongs to year 2021

output= price * 2022factor *2023factor

final measure output - sum of Product A output + Product B output + Product C output

ex. when they select None,

for Product A , measure output is just Price

**expected output when each year selected:**

filter selection in slicer | ||||||

Product | Year | Price | 2021 selected | 2022 selected | 2023 selected | None Selected |

A | 2020 | 10 | 10.35 | 10.71225 | 11.08717875 | 10 |

B | 2021 | 15 | 15 | 15.525 | 16.068375 | 15 |

C | 2022 | 20 | 20 | 20 | 20.7 | 20 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-09-2023
03:28 AM

TotalCost=

VAR selected=SELECTEDVALUE(Year-Factor[Year])

VAR summed = SUMMARIZED('Fact',[Product],[Year],"PY")

VAR year='Year-Factor'[Year]

RETURN

'Fact'[Price]*(year+1,[Factor],"yf")*(yf+1,[Factor])

RETURN

sumx(year,PY)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-08-2023
07:19 PM

`there will be Year slicer in the page from Year-Factor table and user will be choosing the year`

For that to work the Year-Factor table must be disconnected.

Your expected output is unclear for products that fall outside the limits. Please indicate the expected outcome for all products and Year-Filter selections.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-09-2023
02:51 AM

@lbendlin Thanks for the reply. i have added a table at the end of the question which shows the expected output when each value of the Year-Factor slicer is selected. let me know if you need any further information.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-10-2023
10:04 PM

@lbendlin i have added expected outcome for all products and Year-Filter selections. did you get chance to look?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-11-2023
10:52 AM

Adjusted =

VAR yf =

SELECTEDVALUE ( 'Year-Factor'[Year] )

VAR a =

SUMMARIZE (

'Fact',

[Product],

[Year],

"sm",

VAR y = [Year]

RETURN

SUM ( 'Fact'[Price] )

* IF (

yf = "None",

1,

COALESCE (

CALCULATE (

PRODUCTX ( 'Year-Factor', [Factor] ),

'Year-Factor'[Year] <= yf,

'Year-Factor'[Year] > y

),

1

)

)

)

RETURN

SUMX ( a, [sm] )

See attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-20-2023
02:08 AM

Thanks @lbendlin

just last help.

we have altred the Year-Factor table by adding an extra column "Factor2"

Year Factor Factor2

2021 1.035 0.98

2022 1.035 0.98

2023 1.035 0.98

None 1 1

now we need to consider Factor2 in the calculation when Year-Factor.Year < Fact.Year

for rest your given forumula is correct.

ex. for Product D - which belongs to year 2023

when they select 2021

expected output -

output= price * 2022 factor2 * 2023 factor2 (10*0.98*0.98)

when they select 2022

output= price * 2023 factor2 (10*0.98)

expected output. (you just need to add the scenario for the bold one in the current formula)

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-20-2023
10:04 AM

You can change the PRODUCTX calculation accordingly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-21-2023
11:48 AM

@lbendlin tried playing around with that function, couldn't get the exact output. can you please help?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-21-2023
03:16 PM

Have you replaced factor with factor2 in the formula?

it is not clear to me what your expected outcome is. Please clearly indicate the expected outcome for all scenarios.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-21-2023
10:28 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-23-2023
11:26 AM

@lbendlin i have added expected outcome for all scenarios. did you get chance to look?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Announcements

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Check out the November 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

121 | |

98 | |

89 | |

72 | |

62 |

Top Kudoed Authors

User | Count |
---|---|

140 | |

121 | |

107 | |

98 | |

94 |