Complicated Count Measure based on other measures

[villa1980]

Hi all, me again, seems like I am encountering problems on a weekly basis, a lot I can solve but others think it just steps across my knowledge of DAX (bearing in mind only really been using this for over a year 🙂 )

So the new one is that I have a [METRIC] table and in that is a measure called FINAL which looks at 4 seperate measures that return Buckets and then returns a final "Bucket" if not met. I imagine there is a better way to do this but will put those below in order of how the FINAL Bucket is then calculated.

What I want to do is count these "Buckets", by monthyear (which sits in a table called [Calendar] ) and Centre Name (which sits in a table called [CSD] ). Now these 2 tables aren't joined to my metrics one due to 4 buckets measures looking at 4 different tables to return the measures needed for the FINAL measure within the metrics table.

I just don't know how to do this due to how complicated it looks in my eyes, so your help would be appreciated and if there is a better way to get the FINAL bucket rather than build 4 seperate measures to return it.
1. BAU = if ( [Contrib] > 0 , "BAU" , "Other" )

2. Operational = SWITCH (TRUE(), [BAU] = "Other" && [Score_1] < 5 , "Other", [BAU] = "Other" && [Score_1] >= 5 , "Operational","BAU")

3. Headcount = SWITCH(TRUE(),[Operational] = "Other" && OR([Less_Than_3_Heads] = "TRUE",[GPPH_Accept] = "TRUE"), "Headcount",[Operational])

4. Footfall = if ( [Headcount] = "Other" , "Footfall",[Headcount] )

5. FINAL = if ( [Footfall] = "Other" , "Footfall 2" , [Footfall] )

Please don't get me wrong, I do try to overcome these problems and Google can return some weird and wonderful alternatives.

Thanks guys

Alex

[bhanu_gautam]

@villa1980 Ensure that your [METRIC] table is related to the [Calendar] and [CSD] tables. If direct relationships are not possible due to the complexity of your measures, consider using a bridge table or creating calculated columns that can help establish these relationships.

Combine the logic of your measures into a single measure:

FINAL =
VAR ContribCheck = [Contrib] > 0
VAR BAU = IF(ContribCheck, "BAU", "Other")
VAR Operational = SWITCH(TRUE(), BAU = "Other" && [Score_1] < 5, "Other", BAU = "Other" && [Score_1] >= 5, "Operational", "BAU")
VAR Headcount = SWITCH(TRUE(), Operational = "Other" && OR([Less_Than_3_Heads] = "TRUE", [GPPH_Accept] = "TRUE"), "Headcount", Operational)
VAR Footfall = IF(Headcount = "Other", "Footfall", Headcount)
RETURN IF(Footfall = "Other", "Footfall 2", Footfall)

Create a summary table to count the "Buckets" by monthyear and Centre Name:

DAX
SummaryTable =
SUMMARIZE(
ADDCOLUMNS(
[METRIC],
"MonthYear", RELATED([Calendar][MonthYear]),
"CentreName", RELATED([CSD][CentreName]),
"FinalBucket", [FINAL]
),
[MonthYear],

Step 3: Visualize the Data
Use the SummaryTable to create your visualizations in Power BI. You can create a matrix or table visual to display the counts of each "Bucket" by monthyear and Centre Name.

[villa1980]

Wow that is amazing, you have made it sound so much simplier than what I was looking at, thanking you so much.

I am not sure I am able to create a direct relationship between the tables, how would I create a bridging table or calculated columns?