Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count IF Function

Dear All,

 

Please help in correct the below tabular function. I am getting wrong answers in my tabulation.

 

ENQ Item Count = COUNTROWS(FILTER(ALL('Monthly report'),'Monthly report'[Item]=EARLIER('Monthly report'[Item]) || 'Monthly report'[Quotation number]=EARLIER('Monthly report'[Quotation number])))
  • Anonymous's avatar
    Anonymous
    4 years ago

    Fowmy 

     

    Through your guidance i got resolved my out come in tabulation. I have slightly modified your syntex as below. I am getting the desired results as expected with below syntex. 

     

    Thanks for your kind support.

     

    ENQ Item COUNT =
    VAR enqitem =
    MAX ( 'Monthly report'[Item] )-1
    VAR quote =
    ( 'Monthly report'[Quotation number] )
    RETURN
    CALCULATE (
    COUNTROWS ( 'Monthly report' ),
    OR ( 'Monthly report'[Item] = enqitem, 'Monthly report'[Quotation number] = quote ),
    REMOVEFILTERS ( 'Monthly report' )
    )
  • Fowmy's avatar
    Fowmy
    4 years ago

    Anonymous 

    I believe you are creating a Calculated Column NOT a measure. If you are getting the expected results then, it should be fine. 


     

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    Anonymous 

    Not sure where and how you execute this measure. Please try:

    ENQItemCOUNT =
VAR item =
    MAX ( 'Monthly report'[Item] )
VAR quote =
    MAX ( 'Monthly report'[Quotation number] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Monthly report' ),
        OR ( 'Monthly report'[Item] = item, 'Monthly report'[Quotation number] = quote ),
        REMOVEFILTERS ( 'Monthly report' )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Fowmy 

       

      Through your guidance i got resolved my out come in tabulation. I have slightly modified your syntex as below. I am getting the desired results as expected with below syntex. 

       

      Thanks for your kind support.

       

      ENQ Item COUNT =
      VAR enqitem =
      MAX ( 'Monthly report'[Item] )-1
      VAR quote =
      ( 'Monthly report'[Quotation number] )
      RETURN
      CALCULATE (
      COUNTROWS ( 'Monthly report' ),
      OR ( 'Monthly report'[Item] = enqitem, 'Monthly report'[Quotation number] = quote ),
      REMOVEFILTERS ( 'Monthly report' )
      )
      • Fowmy's avatar
        Fowmy
        Super User
        4 years ago

        Anonymous 

        I believe you are creating a Calculated Column NOT a measure. If you are getting the expected results then, it should be fine. 


         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Fowmy 

     

    Thanks for your kind reply and suggession.

     

    I tried with you suggessions and results found "11" in all the rows in my tabulation. 

     

    Note, i have slightly modified your syntex due to error while running the same. Same was pasted in below for your review. Could you please check again and help me to resolve this.

     

    ENQItemCOUNT =
    VAR enqitem =
    MAX ( 'Monthly report'[Item] )
    VAR quote =
    MAX ( 'Monthly report'[Quotation number] )
    RETURN
    CALCULATE (
    COUNTROWS ( 'Monthly report' ),
    OR ( 'Monthly report'[Item] = enqitem, 'Monthly report'[Quotation number] = quote ),
    REMOVEFILTERS ( 'Monthly report' )
    )