Forum Discussion

aymeric_kolan's avatar
aymeric_kolan
Frequent Visitor
9 years ago
Solved

Filter rows

Hi all,   I need your help, I don't find solution alone. I would like identify by ID, #ID where I have only one row with a critea at "Entree_SVI" (see example, results attended is in red).   How...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi aymeric_kolan,

     

    Please use below DAX formula to create a calculated table which only returns three rows that are highlighted in red.

    Table_1 =
CALCULATETABLE (
    'DATA SVI_2',
    FILTER (
        'DATA SVI_2',
        CALCULATE (
            COUNT ( 'DATA SVI_2'[FilterId] ),
            ALLEXCEPT ( 'DATA SVI_2', 'DATA SVI_2'[FilterId] )
        )
            = 1
            && 'DATA SVI_2'[InboundCallVoice_CallId] = "Entree_SVI"
    )
)

    Best regards,
    Yuliana Gu

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago
Measure = IF(CALCULATE(COUNT([InboundCallVoice_CallId]),FILTER('DATA SVI_2',[FilterId]="Entree_SVI")),1,0)
aymeric_kolan's avatar
aymeric_kolan
Frequent Visitor
9 years ago

Hi smoupre.

Thanks but when I used code , it showed "1"
But I would like "3" with my example because there is only 3 ID where there is only one row with "Entree_SVI"

 

Mesure = IF(CALCULATE(COUNT('DATA SVI_2'[InboundVoiceCalls_CallId]);FILTER('DATA SVI_2';[FilterId]="Entree_SVI"));1;0)
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago
     

     Turn it into a column and just SUM the column.

    • aymeric_kolan's avatar
      aymeric_kolan
      Frequent Visitor
      9 years ago

      Thank you for your time but request doesn't return answer I need.

      I need to know how many ID I have with only one row "Entree_SVI".

      When one ID had more than one row, I don't need to count it.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi aymeric_kolan,

         

        Please use below DAX formula to create a calculated table which only returns three rows that are highlighted in red.

        Table_1 =
CALCULATETABLE (
    'DATA SVI_2',
    FILTER (
        'DATA SVI_2',
        CALCULATE (
            COUNT ( 'DATA SVI_2'[FilterId] ),
            ALLEXCEPT ( 'DATA SVI_2', 'DATA SVI_2'[FilterId] )
        )
            = 1
            && 'DATA SVI_2'[InboundCallVoice_CallId] = "Entree_SVI"
    )
)

        Best regards,
        Yuliana Gu