Forum Discussion
mmvohra
Helper II
Helper IINeed help regarding fine measure formulation
I want to make measure as per the following logic: Filter table → only rows where [Dif KM_1] <= ParameterValue. Within each Consolidated ID: Find minimum date. Find the immediate date after the m...
Hi,
Check now
Irwan
Super User
Super Userhello mmvohra
you can do this in various ways, but here i show how i usually do.
you can do this in either calculated column or measure.
1. using calculated column
- create a new table to summarize those ID less than 3000.
Summarize =
SUMMARIZE(
FILTER(
'Work',
'Work'[Dif KM_1]=0||'Work'[Dif KM_1]<=2000
),
'Work'[ID],
'Work'[ConsolidatedID],
'Work'[Distance (km)],
'Work'[Date],
'Work'[Price],
'Work'[Person Type],
'Work'[Name],
'Work'[Link]
)
- create a new calculated column with following DAX for price different.
Price Different =
var _Previous =
MAXX(
FILTER(
'Summarize',
'Summarize'[ConsolidatedID]=EARLIER('Summarize'[ConsolidatedID])&&
'Summarize'[Date]<EARLIER('Summarize'[Date])
),
'Summarize'[Price]
)
Return
IF(
not ISBLANK(_Previous),
'Summarize'[Price]-_Previous
)
- create a new measure for sum value of price different.
Sum Column = SUM('Summarize'[Price Different])
2. using measure
- create a new measure with following DAX for calculating price different.
Price Different =
var _Previous =
MAXX(
FILTER(
ALL('Work'),
'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
'Work'[Date]<SELECTEDVALUE('Work'[Date])&&
'Work'[Dif KM_1]<=2000
),
'Work'[Price]
)
Return
IF(
not ISBLANK(_Previous),
SELECTEDVALUE('Work'[Price])-_Previous
)
- create a new measure with following DAX for sum value.
Sum Measure =
SUMX(
FILTER(
ALL('Work'),
'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
'Work'[Dif KM_1]<=3000
),
[Price Different]
)
- create a slicer for consolidateID filter and KPI visual for showing sum value (either card or KPI or any visual you want)
Hope this will help.
Thank you.
mmvohraHelper II
Helper III want total sum but it should be dynamic based on the value of parameter.
Calculated columns and table are of no use as parameter does not work on them.
I want a measure that can dynamically filter the table based on dif KM_1<=parameter value condition and then find the sum of difference in price meeting the criteria.