cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonie_tollig Frequent Visitor
Frequent Visitor

Filter on colummn

Hi, I have a what I think should be a relatively simple problem which I need to sort in DAX.

Attr AInput ValueRequired Measure
Attr A111
Attr A221
Attr A331
Attr A4 1
Attr A5 1
Attr A666

 

The table above describe the output measure which should be the Input value @ Attr A1 when Attr A = anything except A6. When Attr A = Attr A6 the value should be 6

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter on colummn

Hi @tonie_tollig,

 

According to your description, I think you want to replace result based on current row number, right? (if current row is last row, output last row 'input value' otherwise out first row 'input value')

 

If this is a case, you can add index column in query edit and use following measure to achieve your requirement:

Measure =
VAR _current =
    MAX ( Table1[Index] )
VAR _min =
    MINX ( ALLSELECTED ( Table1[Index] ), [Index] )
VAR _max =
    MAXX ( ALLSELECTED ( Table1[Index] ), [Index] )
RETURN
    IF (
        _current = _max,
        CALCULATE (
            MAX ( Table1[Input Value] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _max )
        ),
        CALCULATE (
            MIN ( Table1[Input Value] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _min )
        )
    )

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
6 REPLIES 6
Super User
Super User

Re: Filter on colummn

Hi,

 

Write this calculated column formula

 

=IF([Attr A]="Attr A6",6,1)

 

Hope this helps.

tonie_tollig Frequent Visitor
Frequent Visitor

Re: Filter on colummn

Hi, thank you for the feedback. Unfortunately I wasn't very clear. I have used the values 1-6 purely as placeholders. The measure should the input value @ Attr A1 where Attr A = Attr A1, Attr A2...Attr A5 but should be the input value @ Attr A6 when Attr A = Attr A6.

 

Hope it makes sense.

 

Super User
Super User

Re: Filter on colummn

I am still not clear.  Someone else who understand your question will help.

tonie_tollig Frequent Visitor
Frequent Visitor

Re: Filter on colummn

Attr AInput ValueRequired Measure
Attr A1Value 1Value 1
Attr A2Value 2Value 1
Attr A3Value 3Value 1
Attr A4 Value 1
Attr A5 Value 1
Attr A6Value 4Value 4

 

 

Community Support Team
Community Support Team

Re: Filter on colummn

Hi @tonie_tollig,

 

According to your description, I think you want to replace result based on current row number, right? (if current row is last row, output last row 'input value' otherwise out first row 'input value')

 

If this is a case, you can add index column in query edit and use following measure to achieve your requirement:

Measure =
VAR _current =
    MAX ( Table1[Index] )
VAR _min =
    MINX ( ALLSELECTED ( Table1[Index] ), [Index] )
VAR _max =
    MAXX ( ALLSELECTED ( Table1[Index] ), [Index] )
RETURN
    IF (
        _current = _max,
        CALCULATE (
            MAX ( Table1[Input Value] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _max )
        ),
        CALCULATE (
            MIN ( Table1[Input Value] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Index] = _min )
        )
    )

14.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tonie_tollig Frequent Visitor
Frequent Visitor

Re: Filter on colummn

Thanks, very intersting way in solving the problemSmiley Happy