Power BI Model help needed

[sentsara]

Hello Friend,

I need your suggestion for linking HEDIS and Revenue at Risk Dataset in Power BI Data model:

above model is not coming as expected.

Below is the Requirement:

Dataset : RevenueatRisk

unique key for joiner column: HEDIS Population,Program Category,Program,State Program,Measure

HEDIS Population	Program Category	Program	State Program	Measure	Goal
Medicaid	1.Revenue at Risk	Auto Assign	MCO Scorecard	BCS	80.00%
Medicaid	1.Revenue at Risk	Withhold	P4Q	CCS	75.00%
Medicaid	2.Publicly Reported	State Report card	State Report card	CBP	60.00%
Medicaid	1.Revenue at Risk	Withhold	P4Q	CCS	90.00%
Medicare	3.Medi	Auto Assign	MCO Scorecard	UTL	95.00%

Dataset: HEDIS

Unique joiner column: HEDISPopulation,Measure

HEDIS Population	Measure	Num	Deno	Compliance Rate
Medicaid	IBR	10	50	20.00%
Medicaid	CPU	20	30	66.67%
Medicaid	BCS	25	50	50.00%
Medicaid	CCS	35	60	58.33%
Medicaid	CDC	45	80	56.25%
Medicaid	CBP	55	120	45.83%
Medicare	UTL	500	1500	33.33%

Expected output in Tabular visual:

HEDIS Population	Program Category	Program	State Program	Measure	Num	Deno	Compliance Rate	Goal
Medicaid	1.Revenue at Risk	Auto Assign	MCO Scorecard	BCS	25	50	50.00%	80.00%
		Withhold	P4Q	CCS	35	60	58.33%	75.00%
				CCS	35	60	58.33%	90.00%
	2.Publicly Reported	State Report card	State Report card	CBP	55	120	45.83%	60.00%
Medicare	3.Medi	Auto Assign	MCO Scorecard	UTL	500	1,500	33.33%	95.00%
Medicaid	N/A	N/A	N/A	IBR	10	50	20.00%	-
CPU	20	30	66.67%	-
CDC	45	80	56.25%	-

need to the best model , how to break the revenueatRisk dataset into more than one table and link properly.

just can anyone throw an idea for the best module creation on this requirement?

[sentsara]

All the measures won't be available in Revene at risk table. Only the measures which have goals , will be having entry to this table. I just put some sample records , but in practical large data will be there.

main table here is HEDIS ( Fact). All measures will exists here. so we need to correct the solution to pickup all data from HEDIS And left join with revenue at Risk table ( for picking up program category,program , state program,goal)

[MFelix]

Hi @sentsara ,

I have one question regarding your dataset you have on measure CCS two values for Goal is that correct?

I created two tables one for population and another for the Measures then related this two tables with the others:

Since using a matrix will summarize the information on the values and you have goals for the same measure you need to make one workaround that is to place the goal on the rows also, if you need to have it on the values, then you must also pace it on the values and reduce the size of the column goal:

On the second image you can see the table with the goal hidden:

[sentsara]

Hi Miguel Félix,

Thanks for taking your time and providing me the solution.

I still need to get IBR ,CPU and UTL Measure  with the compliance Rate in the Matrix.

[MFelix]

Hi @sentsara ,

I have looked at your issue and the questions is about the relationships and the fact that you have some details that are in one of the table but not on the other one, in this case the lowest detail of infomration at program category.

The fastest way is to add the rows that you need on the Revenue at risk but without any values, in this case for the MEDICAID IBR and CPU and for Medicare UTL only add the  Population and the measure leave the rest of the fields in blank.

If this is not possible please tell me and I can try and check another soluiton.

[sentsara]

All the measures won't be available in Revene at risk table. Only the measures which have goals , will be having entry to this table. I just put some sample records , but in practical large data will be there.

main table here is HEDIS ( Fact). All measures will exists here. so we need to correct the solution to pickup all data from HEDIS And left join with revenue at Risk table ( for picking up program category,program , state program,goal)