The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.

**2-for-1 sale on June 20 only!**

- Power BI forums
- Updates
- News & Announcements
- 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
- Custom Visuals Community Blog
- 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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- 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

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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

Featured Topics

Top Solution Authors

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

106 | |

101 | |

82 | |

62 | |

55 |

Top Kudoed Authors

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

252 | |

119 | |

115 | |

95 | |

70 |