Using Two Filters in a Calculate to get one value

[Anonymous]

Hello to all

This must be very simple but I'm stuck.

I have a table of labels with the following structure:
id    label     labeltypeid   issueid

1     test               1             125

2     version          2             125

3     test               1              126

4     test                1              127

5     test                1              128

6     version           2             128

and table issues

issueid    summary

125            issue 1

126            issue 2

127            issue 3

128            issue 4

I want to add in table issues a column with the label that corresponds to labeltypeid   = 1

Till know I have this:

VAR LabelTentative = FILTER ( labels ; labels [labeltypeid] = 1 )
VAR LabelTentativeFilter = FILTER (
        LabelTentative;
       issues[issueid] = label[issueid]
    )
RETURN

After this tried TOPN, calculate.. and always with no sucess

If I had for example COUNTROWS(LabelTentativeFilter) I get the corret number by issueid.

What am I doing wrong?

Thanks

[Anonymous]

Last attempt:

Tentative Release Label =
VAR LabelTentative = FILTER ( labels ; labels [labeltypeid] = 1 && 'issues'[issueid] = labels[issueid])
RETURN
CALCULATE(MAX(labels[label]);TOPN(1;LabelTentative))

MAX does not work because I have a string.

[Vvelarde]

@Anonymous

Hi, if both tables are related you can use this:

Column =
CALCULATE (
    VALUES ( Labels[label] ),
    FILTER ( RELATEDTABLE (Labels), Labels[labeltypeid] = 1 )
)

Regards

Victor

[Anonymous]

@Vvelarde

Yes they are related, and it seems your formula works.. but I have multiple rows with diferent labels, so I get the Multiple values error.

How can we change it so I can get only the last one (highest ID)?

TopN on the filter?

[Vvelarde]

@Anonymous

Try with this:

Column =
CALCULATE (
    SELECTEDVALUE ( Labels[label] ),
    TOPN (
        1,
        FILTER ( RELATEDTABLE ( Labels ); Labels[labeltypeid] = 1 ),
        Labels[id], DESC
    )
)

[Anonymous · Accepted solution]

Hello  @Vvelarde

I ended up using a variation of your formula:

CALCULATE (
    VALUES ( Labels[label] );
    TOPN (
        1;
        FILTER ( RELATEDTABLE (labels); labels[labeltypeid] = 1 );
        Labels[id]; DESC
    )
)

and it is working. 🙂

Thanks