Get the max value of a range of rows

[Anonymous]

Hello to all

Starting my journey on power BI and after searching several times I could not find an answer to my question, so here it goes.

Consider the following data:

Id        Value

1          4

1          2

2          5

3          8

3          5

What I need to have is something that gives me the following output:

Id=1 Max=4

id=2 Max=5

Id=3 Max=8

So that I can use Mas values to get data from another table.

Is this possible? and how? 

Thanks

Rmachado

PS - (the main problem is that a 1 to many relationship that does not allow me to use lookupvalue)

[Baskar]

basically Max will help u .

but i think u r looking on another table , am i right ?

1. create fact - less - fact table to create relationship between that two tables.

     Example : create new table using dax query only relationship with distinct value .

                         New Table = Values ( Key Column Name ) --- It will give u the unique value on that column

thyen create relation ship.

let me know if any help dude.

[Anonymous]

@Anonymous,

This measure will give you the output you are looking for:

Max Value  = MAX ( YourTable[Value] )

But I'm not sure how you'll use that for a lookup without seeing more of your data model.  You mention that "the main problem is that a 1 to many relationship that does not allow me to use lookupvalue".  If you can describe your datasets (sample data and relationships), and what output you are trying to get then we may be able to solve your underlying issue.

[Anonymous]

Thanks for the answer, but in that case max will return the highest value of the all column, and I need the highest value by id. (if I understand the MAX function).

The data model:

My data is the following:

Using the id on the table on the left (above image):

I get id =10438

Left table as you can see has multiple issueid with 10438.

So I need to get to Planned days but when I try to fetch the value I get the multiple values error (which is correct).  So how can we solve this?  

Transform the data? Pivot? Join?

Thanks

[Anonymous]

@Anonymous,

Sorry, I find the data model a little hard to follow.  I assume the left-hand, unnamed table is something like 'Issue' ?

Issue[id] = 10438 is linked to 'public changeitem'[groupid] = 50997, 50256, 50034 etc. ?

And you only want to extract the [Planned Days] for the largest (latest?) 'public changeitem'[groupid] - i.e. for this one, you only want the planned days for [issueid] = 10438 to be the 20 from [groupid] = 50997 ?

Will this measure do what you need:

Last Planned Days =
CALCULATE (
    SUM ( 'public changeitem'[Planned Days] ),
    FILTER (
        'public changeitem',
        'public changeitem'[groupid] = MAX ( 'public changegroup'[id] )
    )
)

 Or this:

Last Planned Days =
CALCULATE (
    LOOKUPVALUE (
        'public changeitem'[Planned Days],
        'public changeitem'[groupid], MAX ( 'public changegroup'[id] )
    )
)

Thinking further, why not simplify your model - can you merge [issueid] from 'public changegroup' into 'public changeitem' and remove the need for table 'public changegroup' ?

[Anonymous]

Hello again

I merge the two tables but.. I'm stuck with the same problem.

Now my data as a timestamp (this works as a history table), so I can try to get the last value by date, but I need to filter it dinamically.

Example:

56253	10543	14/12/2016 17:25	R&D planned effort (man-days)		25
56267	10543	14/12/2016 17:33	R&D planned effort (man-days)		20
56283	10543	14/12/2016 23:35	R&D Sponsor	john	Daniel

In this case I need to first filter the data usin DAX function FILTER by "R&D planned effort (man-days)" and then get the last value by date (in this case would be 20).

I manage to get the FILTER part, but the rest.. no. 😞

Can you help?

Thanks

[Anonymous]

@Anonymous,

Sorry, I'm confused as to how you generated the example table.  

You merged public changegroup' with'public changeitem' ?

Where do "R&D planned effort (man-days)" and "R&D Sponsor" and "John" and "Daniel" come from - are these [oldvalue], [oldstring], [newvalue] column values from the 'public changeitem'?

Can you share your query and include column headers in the table extract?

[Anonymous]

Hello again @Anonymous

You merged public changegroup' with'public changeitem' ?

 Yes, since in fact for each changegroup row there was a changeitem row. In the model image I posted I have removed some fields of the changegroup to make it easier for me to move around with the data.

Where do "R&D planned effort (man-days)" and "R&D Sponsor" and "John" and "Daniel" come from - are these [oldvalue], [oldstring], [newvalue] column values from the 'public changeitem'?

id	issueid	creation date	field	oldstring	newstring
56253	10543	14/12/2016 17:25	R&D planned effort (man-days)		25
56267	10543	14/12/2016 17:33	R&D planned effort (man-days)		20
56283	10543	14/12/2016 23:35	R&D Sponsor	john	Daniel

The query I'm used (not correct but just to give a value) is this one.

Column =
CALCULATE(
max('public changegroup'[created]);
FILTER('public changegroup';'public changegroup'[Field] = "R&D planned effort (man-days)");
FILTER('public changegroup';'public changegroup'[issueid] = 'public issuetable'[id])
)

I'm assuming the filters will give me the first two rows so after this I wat to get the 20 because that is the most recent date. In my formula i'm getting the date and not the newstring value.

This table is a huge history table that has all the changes made in all the fields in each issuedid.

Do you have all you need to help me? 🙂

Many thanks

Rui

[Anonymous]

@Anonymous

Also try this one:

CALCULATE(
LOOKUPVALUE('public changegroup (3)'[created];'public changegroup (3)'[NewColumn.newstring];format(max('public changegroup (3)'[created]);"General Date"));
FILTER('public changegroup (3)';'public changegroup (3)'[NewColumn.Field] = "R&D planned effort (man-days)");
FILTER('public changegroup (3)';'public changegroup (3)'[issueid] = 'public jiraissue (2)'[id])
)

But the "many values instead of one" error appear. This strikes me as odd because max should return only one value...

[Anonymous · Accepted solution]

@Anonymous,

So it looks Planned Days is no longer a distinct column, but carried as a value in [newstring], and [creation date] is a date/time stamp in this History table?

You can't use MAX for a text column - [newstring] also contains "Daniel" etc.  So you need to 'do something like calculate' the 'value' for [newstring] after getting the latest [creation date] row for the (related) [issueid] via TOPN. Try this though it may be slow on a large table:

Last Planned Days =
CALCULATE (
    VALUES ( 'public changegroup'[newstring] ),
    TOPN (
        1,
        FILTER (
            'public changegroup',
            'public changegroup'[field] = "R&D planned effort (man-days)"
        ),
        'public changegroup'[creation date], DESC
    )
)

[Anonymous]

@Anonymous, Actually Planned Days was a name I gave to the column, but it was filtered query and did not serve our goal. 

The rest is exactly as you say. new string is the value of the field and creation date the date the change it was made.

You query in fact did return a value but it gave the top value in all values. 😞 The problem (I think) is that I also need to filter the issueid.

if it was SQL it would be somethinh like:

select top(newstring) where issued=12345 and field="R&D planned effort (man-days)" order by creation date desc (if i'm correct).

Can I filter the filter?

[Anonymous]

Well @Anonymous

Using your formula and filtering the filter:

Last Planned Days =
CALCULATE (
    VALUES ( 'public changegroup (3)'[newstring] );
    TOPN (
        1;
        FILTER (
            FILTER('public changegroup (3)';'public changegroup (3)'[issueid] = 'public issue'[id]);
            'public changegroup (3)'[field] = "R&D planned effort (man-days)"
        );
        'public changegroup (3)'[created]; DESC
    )
)

It worked!!

Thanks very muck.

[Anonymous]

@Anonymous,

It's good to hear you got it sorted.  Note that the measure I suggested should work fine in a table/matrix with Issue as a row.

Cheers.

[Anonymous]

Well. One problem solved with the help of @Anonymous.

but now I have another and it is related.

This is a String and some values are no filled, but I need to calculate the sum of the values that are filled.

I tried Format but It did not work. Changed the format type of the column, di not work either. It would not allow me to make a SUM only count in another visual (Gauge).

How can I solve it? Using isBlank? Change the empty to 0?

[Anonymous]

This maybe?

Last Planned Days =
CALCULATE (
    VALUES ( 'public changegroup'[newstring] ),
    TOPN (
        1,
        FILTER (
            'public changegroup',
            'public changegroup'[field] = "R&D planned effort (man-days)" &&
            NOT( ISBLANK( 'public changegroup'[newstring] ) )
        ),
        'public changegroup'[creation date], DESC
    )
)

[Anonymous]

It did not worked :(.

I will try to FORMAT it, but if you have an ideia, just let me know.

[Anonymous]

@Anonymous,

When you say id did not work, what error message or incorrect output are you getting?

[Anonymous]

It gave me the data type variant field error.

I manage to solve it by doing a changing the query that gives me the values. This is not the good solution but it worked in this case. Now the values arrive as number so I can do sum, average and so on.

Thanks for the help.

[Anonymous]

Thanks for the answer.

I will try both your ideas, but the last one strikes me as the best approach. This data model is tricky. 🙂