Forum Discussion

mmvohra's avatar
mmvohra
Icon for Helper II rankHelper II
6 months ago
Solved

Need 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...
Need Help
Show and Tell
Irwan's avatar
Irwan
Icon for Super User rankSuper User
6 months ago

hello 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.

  • mmvohra's avatar
    mmvohra
    Icon for Helper II rankHelper II
    6 months ago

    I 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.