Metric Calculations and Data

[Anonymous]

I was wondering if someone could help me with this setup.  I am certain that I need Dax, but I am having a hard time how to use Dax to set up these metrics.  In the attached I have the setup for Power BI – Slide 1 is the Data set up and metric calculations and Slide 2 is the report set up.

Data Table

There is the metric table which has condition prevalence – the numerator is the combo of a report case ID, report rule ID (to identify the metric), result flag and comp flag (to indicate prevalence).  The denominator is just the combo of a report case ID, report rule ID.

Metric Table
Run #	MBR_KEY	RPT_Case_ID	Rpt_Rule_ID	Result_Flag	Comp_Flag
2	357975	100005	90001	YES	1
3	483289	100005	90002	YES	1
4	320471	100004	90003	YES	1
7	982766	100003	90002	YES	1

Metric Calculations (example)

Numerator

Asthma = Rpt_Case_ID=10005, Rpt_Rule_ID=90001, Result_Flag=“Yes”, Comp_Flag=1

Denominator

Asthma = Rpt_Case_ID=10005, Rpt_Rule_ID=90001

There is also the member key to identify the person and the run number that corresponds to the quarter.

Member Table

Has the run number, member key and other filter data such as city, age, etc

Member Table
Run #	MBR_KEY	City	Status	Age	Gender
2	357975	Chicago	Active	25	F
3	483289	Miami	Active	32	M
4	320471	DC	Retired	54	F
7	982766	Atlanta	Retired	72	M

Report

I need to create measure for the metrics and I have been using the Calculate function in Dax, but I am having problems with the multiple conditions (report case id, report rule id, etc) to create the numerator and then the denominator.

Metric Table
Metric	Current Qtr	Prior Qtr	Prior Year	QoQ	YoY
Asthma	31.2%	31.5%	32.6%	(0.5%)	(2.6%)
Cancer	0.2%	0.5%	0.6%	(0.5%)	(2.6%)
Hypertension	41.2%	41.5%	42.6%	(0.5%)	(2.6%)
Diabetes	34.2%	34.5%	34.6%	(0.6%)	(2.9%)

Filters

City

Health Plan

Status

Age

Then I am not sure how to use the segmentations in the report – city, status, etc

Could someone please help point me in the right direction?

Thanks so much!!

[lbendlin · Accepted solution]

Your data model is all sideways.  You may want to re-organize it into a standard star schema before proceeding.  While not strictly requiring it, Power BI does like the concept of "dimension" tables (filters) and "fact" tables (values to be computed). Your member table shouldn't include the Run # for example. And you may want to add a Calendar dimension.