Power Bi Many to Many Related Fact & Dim Data Model - Calculated measures not working properly

[KarthikUS]

Hi Team,

We are facing some challenges in my current project while creating calculated measures with current data model which brings many-many relationship in Power BI.

I’ve explained the scenario below. Any experts , please help us on this issue. Appreciating the volunteers who come forward to help us.

Current Scenario:

As per the below Fact and Dimension dataset,

• Fact has the granularity at 4 columns State, Population, Measure, Submeasure.
• But the Dimension has granularity level at 7 columns State, Population, Measure, Submeasure, Program Group, Program, State Program.
• Joiner for relationship is based on 4 columns State, Population, Measure, Submeasure.
• For Many to Many Relationship we are using bridge table having same above 4 column sets between these 2 datasets.

Question:

In below scenario, we couldn’t able to achieve the calculated measure count and goal met accordingly. As the model has different granularity level and the calculated measure comparison between Fact and Dim dataset. so please let us know do we have any solution for this method of many to many modelling to achieve the expected output as mentioned below.

Many to Many Relation Modeling

(i.e., Below should be considered as Goal Met Yes  and No at Program Level irrespective of Provider)

Required output measure:

CALCULATED MEASURE TOTAL GOAL COUNT: = 4

CALCULATED MEASURE GOAL COUNT - Goal Met Yes := 2

CALCULATED MEASURE GOAL COUNT - Goal Met No:= 2

Thanks in Advance!!

[Anonymous]

@KarthikUS 
Can you show the above mode in a pbix, so I know see which columns are connected to each other. Also I need to some test to see if I can get the expected output. Thank you.

Best Regards
Paul