Forum Discussion

InsureBI's avatar
InsureBI
Advocate II
9 years ago
Solved

Multiple Filters COUNTROWS - Too Few Arguments Passed

I have a "Policy" table with the following information:

 

Policy No., Issued_Date, Premium, Policy_Type

10001,1/1/2016,250,P

10002,1/1/2016,300,P

10003,1/2/2016,260,S

10004,1/2/2016,320,P

10005,1/3/2016,250,S

10006,1/3/2016,310,P

10007,1/4/2016,360,P

10008,1/4/2016,300,S

 

I want to create a table and a graph that shows cumulative number of policies (or records) by issued date and by policy type:

Date,Policy Type P, Policy Type S

 1/1/2016,2,0

1/2/2016,3,1

1/3/2016,4,2

1/4/2016,5,3

 

I created a new measure called Cumulative_Policy_Count_P, for Policy Type P:

 

Cuml_Pol_Count_P = CALCULATE (
COUNTROWS (Policy),
FILTER (ALLSELECTED(Policy),
Policy[ISSUED_DATE] <= MAX (Policy[ISSUED_DATE])),
FILTER(Policy[POLICY_TYPE] = "P")
)

 

This results in an error : Too few arguments were passed to FILTER function.

 

What am I doing wrong?

 

Thanks.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    InsureBI

     

    In addition to ankitpatira's solution, you should be able to create a measure to calculate the cumulative number of policies (or records) by issued date and by policy type as you mentioned above. The formulas below are for your reference.

    Cuml_Pol_Count_P = 
CALCULATE (
    IF ( ISBLANK ( COUNTROWS ( Policy ) ), 0, COUNTROWS ( Policy ) ),
    FILTER (
        ALLSELECTED ( Policy ),
        Policy[ISSUED_DATE] <= MAX ( Policy[ISSUED_DATE] )
            && Policy[POLICY_TYPE] = "P"
    )
)

    Cuml_Pol_Count_S = 
    CALCULATE (
     IF ( ISBLANK ( COUNTROWS ( Policy ) ), 0, COUNTROWS ( Policy ) ),
     FILTER (
     ALLSELECTED ( Policy ),
     Policy[ISSUED_DATE] <= MAX ( Policy[ISSUED_DATE] )
     && Policy[POLICY_TYPE] = "S"
     )
    )

    Following is the result of the measures in report.

    Regards

3 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    InsureBI You can do that by going to power bi desktop query editor -> right click your original table and create duplicate -> remove column Premium -> hightlight column Policy_Type and under Transform tab clikc Pivot column -> under Values select Policy_no and under Advanced option select Count(All). This will give you table in your preferred output.

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

    InsureBI

     

    In addition to ankitpatira's solution, you should be able to create a measure to calculate the cumulative number of policies (or records) by issued date and by policy type as you mentioned above. The formulas below are for your reference.

    Cuml_Pol_Count_P = 
CALCULATE (
    IF ( ISBLANK ( COUNTROWS ( Policy ) ), 0, COUNTROWS ( Policy ) ),
    FILTER (
        ALLSELECTED ( Policy ),
        Policy[ISSUED_DATE] <= MAX ( Policy[ISSUED_DATE] )
            && Policy[POLICY_TYPE] = "P"
    )
)

    Cuml_Pol_Count_S = 
    CALCULATE (
     IF ( ISBLANK ( COUNTROWS ( Policy ) ), 0, COUNTROWS ( Policy ) ),
     FILTER (
     ALLSELECTED ( Policy ),
     Policy[ISSUED_DATE] <= MAX ( Policy[ISSUED_DATE] )
     && Policy[POLICY_TYPE] = "S"
     )
    )

    Following is the result of the measures in report.

    Regards

    • InsureBI's avatar
      InsureBI
      Advocate II
      9 years ago

      Thanks to Ankitpatira and JerryLi for posting the solutions.  I chose to go with Jerry's solution (and it worked!) to avoid duplicating data, but I will use Ankitpatira's solution if I find myself creating too many new measures.