Forum Discussion

tonie_tollig's avatar
tonie_tollig
Frequent Visitor
7 years ago
Solved

Filter on colummn

Hi, I have a what I think should be a relatively simple problem which I need to sort in DAX. Attr A Input Value Required Measure Attr A1 1 1 Attr A2 2 1 Attr A3 3 1 Attr A4  ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 )
        )
    )

     

    Regards,

    Xiaoxin Sheng

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Write this calculated column formula

 

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

 

Hope this helps.

tonie_tollig's avatar
tonie_tollig
Frequent Visitor
7 years ago

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.

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

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

    • tonie_tollig's avatar
      tonie_tollig
      Frequent Visitor
      7 years ago
      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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 )
        )
    )

         

        Regards,

        Xiaoxin Sheng