Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |