Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
InsureBI
Advocate II
Advocate II

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.

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

@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.

r5.PNG

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

@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.

r5.PNG

Regards

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.

ankitpatira
Community Champion
Community Champion

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.