Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago

Complicated Count Measure based on other measures

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




 

2 Replies

  • 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's avatar
      villa1980
      Resolver II

      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?