topic Filter values for calculating probability in DAX Commands and Tips

Filter values for calculating probability

Anonymous — Thu, 02 Jan 2020 11:10:18 GMT

Hi,

Scenario: I have a history Data which consists of many records. I Just want to Filter the history record if value changes.

so now i just want to filter both

1. before change record(latest) and

2. Changed record

or can i change record field is current value to true if any field value changes?

I Have History Data in PowerBi Query in the Following Manner.

Id	Value	Date	Is Current
58667	1	07-11-2019	FALSE
58667	1	08-11-2019	FALSE
58667	1	09-11-2019	FALSE
58667	1	10-11-2019	FALSE
58667	2	11-11-2019	TRUE

How can i filter the history Data(Last Two rows only to use that in my chart) in the following Manner.

Id	Value	Date	Is Current
58667	1	10-11-2019	TRUE
58667	2	11-11-2019	TRUE

Thanks in Advance!! Have a Great Day!

Re: Filter values for calculating probability

az38 — Thu, 02 Jan 2020 16:06:06 GMT

Hi Anonymous

try a calculated table

Table = UNION( summarize( FILTER('Table1';'Table1'[Is Current]=FALSE()); Table1[Id];Table1[Value];Table1[Is Current]; "Date";MAX(Table1[Date]) ); summarize( FILTER('Table1';'Table1'[Is Current]=TRUE()); Table1[Id];Table1[Value];Table1[Is Current]; "Date";MIN(Table1[Date]) ) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Filter values for calculating probability

Anonymous — Fri, 03 Jan 2020 06:01:22 GMT

Thanks az38

But this does not satisfy my scenario. it fails when

Id	Value	Date	Is Current
58667	1	07-11-2019	FALSE
58667	1	08-11-2019	FALSE
58667	1	09-11-2019	FALSE
58667	1	10-11-2019	FALSE
58667	2	12-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	14-11-2019	FALSE
58667	3	15-11-2019	TRUE

Den it should return

Id	Value	Date	Is Current
58667	1	10-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	15-11-2019	TRUE

It should get updated or saved when value changes

NOTE: Id is Same for all.

or If Record/row Value changes. can we change the Is Current value of previous record/row to true. so that we can filter by

Is Current = true

Thanks,

Sandeep.

Re: Filter values for calculating probability

az38 — Fri, 03 Jan 2020 06:44:13 GMT

Hi Anonymous

what value should be for the latest value?

in first post you wrote

Anonymous wrote:
Changed record

But in the last example it's also last row

58667

15-11-2019

TRUE

if your first task was correct try a caluclated table

Table = UNION( summarize( FILTER(Table1;'Table1'[Value]<calculate(max(Table1[Value]);ALLEXCEPT(Table1;Table1[Id]))); Table1[Id];Table1[Value];Table1[Is Current]; "Date";MAX(Table1[Date]) ); summarize( FILTER(Table1;'Table1'[Value]=calculate(max(Table1[Value]);ALLEXCEPT(Table1;Table1[Id]))); Table1[Id];Table1[Value];"Is Current";FIRSTNONBLANK(Table1[Is Current];1); "Date";MIN(Table1[Date]) ) )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Filter values for calculating probability

Mariusz — Fri, 03 Jan 2020 08:21:36 GMT

Hi Anonymous

Please see the below DAX expression, hope it is what you looking for.

Table 2 = VAR __tbl = GROUPBY( 'Table', 'Table'[Id], 'Table'[Value], "Date", MAXX( CURRENTGROUP(), 'Table'[Date] ) ) RETURN CALCULATETABLE( 'Table', TREATAS( __tbl, 'Table'[Id], 'Table'[Value], 'Table'[Date] ) )

I would, however, suggest that you try Power Query for this type of transformation.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.