Forum Discussion

Szokens's avatar
Szokens
Helper II
1 year ago
Solved

Calculated table column values changes on slicer selection

Hi, maybe someone will be able to advise. I have created field parameter as follows: Timeframe_table = {     ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),     ("Monthly", NAMEOF('dmn_date'[la...
  • Szokens's avatar
    Szokens
    1 year ago

    Hi!

    This is actually fantastic, I have adapted your ideas into my use case and here is the output. One barchart changing dimensions and breakdown type dynamically ( example screenshots):

     

     

     

    Below I put formulas for each field I have included in the visual settings if anyone would be interested in this solution:

     

    i) X Axis - "Timeframe_Table[Timeframe]", field from disconnected field parameter table (data comes from date table related to our fact table in relation [dmn.date] 1 - * [dmn.email]):

    Timeframe_table = {
        ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),
        ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),
        ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),
        ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)
     
    ii) Y Axis - dmn.email[User_Count_by_Flag]
    User_Count_by_Flag =
    VAR SelectedFlag = SELECTEDVALUE(Timeframe_table[Parameter Order])
    VAR SelectedLegend = SELECTEDVALUE(LegendValues[Flag])
    VAR Result0 =
        SWITCH(
            SelectedFlag,
            0, CALCULATE(
                    DISTINCTCOUNT(dmn_email[sender_email]),
                    dmn_email[New/Old User day] = SelectedLegend
                ),
            1, CALCULATE(
                    DISTINCTCOUNT(dmn_email[sender_email]),
                    dmn_email[New/Old User week] = SelectedLegend
                ),
            2, CALCULATE(
                    DISTINCTCOUNT(dmn_email[sender_email]),
                    dmn_email[New/Old User month] = SelectedLegend
                ),
            3, CALCULATE(
                    DISTINCTCOUNT(dmn_email[sender_email]),
                    dmn_email[New/Old User quarter] = SelectedLegend
                )
        )
    VAR Result1 = DISTINCTCOUNT(dmn_email[sender_email])
    RETURN
    SWITCH([Selected_Breakdown_by],
    0,Result0,
    1,Result1)
     
    iii) Legend - "Parameter_Breakdown_By2[Parameter_Breakdown_By2]", field from disconnected field parameter table (data comes from another disconnected table, formula provided below + employee table related to our fact table in relationship like [v_sdp_dim_employee_curr] 1 - * [dmn_email] )
    Parameter_Breakdown_By2 = {
        ("New vs Returning", NAMEOF('LegendValues'[Flag]), 0),
        ("Business Area", NAMEOF('v_sdp_dim_employee_curr'[gcrs_busin_area_name]), 1)
    }
     
    Other required fields:
    iv) disconnected table as follows:
    LegendValues =
    DATATABLE(
    "Flag", STRING,
    {{"New"},{"Returning"}}
    )
     
    *i am not posting all formulas fields dmn_email[New/Old User...] just an example on "Weekly" field:
    v) Min sent date weekly = CALCULATE(MIN(dmn_date[last_day_of_week]),ALLEXCEPT(dmn_email,dmn_email[sender_email]))
     
    vi) New/Old User week =
    IF(related(dmn_date[last_day_of_week])>dmn_email[Min sent date weekly],"Returning","New")
     
    thanks again for support!
v-tejrama's avatar
v-tejrama
Community Support
1 year ago

Hi Szokens ,

 

Enable dynamic time granularity options (Daily, Weekly, Monthly, Quarterly) on the X-axis. Display user counts split by "New" and "Returning" based on different flag columns (C1–C4). Keep full filter context, including sent_date, business title, area, and internal/external. Address Power BI’s limitation regarding placing measures directly in the legend.

 

FIELD PARAMETER FOR TIME AXIS (Already Set Up)
Timeframe_table = {
    ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),
    ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),
    ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),
    ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)
}

Assign Timeframe_table[Timeframe] to the visual’s X-axis.

 

CREATE DISCONNECTED FLAG SOURCE SELECTOR TABLE
FlagSourceSelector = DATATABLE(
   "FlagSource", STRING,
   {
       {"C1"},
       {"C2"},
       {"C3"},
       {"C4"}
   }
)

Use this table in a slicer so users can choose which C column (C1–C4) to analyze.

 

LEGEND TABLE
LegendValues = DATATABLE(
   "Flag", STRING,
   {
       {"New"},
       {"Returning"}
   }
)

Use this table for the bar chart legend.

 

FINAL MEASURE WITH CONTEXTUAL LOGIC
User Count by Flag =
VAR SelectedFlag = SELECTEDVALUE(FlagSourceSelector[FlagSource])
VAR SelectedLegend = SELECTEDVALUE(LegendValues[Flag])
VAR Result =
    SWITCH(
        SelectedFlag,
        "C1", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C1_Flag] = SelectedLegend
            ),
        "C2", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C2_Flag] = SelectedLegend
            ),
        "C3", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C3_Flag] = SelectedLegend
            ),
        "C4", CALCULATE(
                DISTINCTCOUNT(dmn_email[user_id]),
                dmn_email[C4_Flag] = SelectedLegend
            )
    )
RETURN Result

 

Update C1_Flag, C2_Flag, etc. with your actual column names containing "New" or "Returning".

 

VISUAL CONFIGURATION
X-axis: Timeframe_table (calendar_date / week / month / quarter)

Legend: LegendValues[Flag]
Values: Measure: User Count by Flag

 

Thank you.

v-tejrama's avatar
v-tejrama
Community Support
1 year ago

Hi Szokens ,

 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

 

Thank you.

 

  • v-tejrama's avatar
    v-tejrama
    Community Support
    1 year ago

    Hi Szokens ,

     

    I hope the information provided has been useful. Please let me know if you need further clarification

     

    Thank you.

    • Szokens's avatar
      Szokens
      Helper II
      1 year ago

      Hi!

      This is actually fantastic, I have adapted your ideas into my use case and here is the output. One barchart changing dimensions and breakdown type dynamically ( example screenshots):

       

       

       

      Below I put formulas for each field I have included in the visual settings if anyone would be interested in this solution:

       

      i) X Axis - "Timeframe_Table[Timeframe]", field from disconnected field parameter table (data comes from date table related to our fact table in relation [dmn.date] 1 - * [dmn.email]):

      Timeframe_table = {
          ("Daily", NAMEOF('dmn_date'[calendar_date]), 0),
          ("Monthly", NAMEOF('dmn_date'[last_day_of_month]), 2),
          ("Weekly", NAMEOF('dmn_date'[last_day_of_week]), 1),
          ("Quarterly", NAMEOF('dmn_date'[last_day_of_quarter]), 3)
       
      ii) Y Axis - dmn.email[User_Count_by_Flag]
      User_Count_by_Flag =
      VAR SelectedFlag = SELECTEDVALUE(Timeframe_table[Parameter Order])
      VAR SelectedLegend = SELECTEDVALUE(LegendValues[Flag])
      VAR Result0 =
          SWITCH(
              SelectedFlag,
              0, CALCULATE(
                      DISTINCTCOUNT(dmn_email[sender_email]),
                      dmn_email[New/Old User day] = SelectedLegend
                  ),
              1, CALCULATE(
                      DISTINCTCOUNT(dmn_email[sender_email]),
                      dmn_email[New/Old User week] = SelectedLegend
                  ),
              2, CALCULATE(
                      DISTINCTCOUNT(dmn_email[sender_email]),
                      dmn_email[New/Old User month] = SelectedLegend
                  ),
              3, CALCULATE(
                      DISTINCTCOUNT(dmn_email[sender_email]),
                      dmn_email[New/Old User quarter] = SelectedLegend
                  )
          )
      VAR Result1 = DISTINCTCOUNT(dmn_email[sender_email])
      RETURN
      SWITCH([Selected_Breakdown_by],
      0,Result0,
      1,Result1)
       
      iii) Legend - "Parameter_Breakdown_By2[Parameter_Breakdown_By2]", field from disconnected field parameter table (data comes from another disconnected table, formula provided below + employee table related to our fact table in relationship like [v_sdp_dim_employee_curr] 1 - * [dmn_email] )
      Parameter_Breakdown_By2 = {
          ("New vs Returning", NAMEOF('LegendValues'[Flag]), 0),
          ("Business Area", NAMEOF('v_sdp_dim_employee_curr'[gcrs_busin_area_name]), 1)
      }
       
      Other required fields:
      iv) disconnected table as follows:
      LegendValues =
      DATATABLE(
      "Flag", STRING,
      {{"New"},{"Returning"}}
      )
       
      *i am not posting all formulas fields dmn_email[New/Old User...] just an example on "Weekly" field:
      v) Min sent date weekly = CALCULATE(MIN(dmn_date[last_day_of_week]),ALLEXCEPT(dmn_email,dmn_email[sender_email]))
       
      vi) New/Old User week =
      IF(related(dmn_date[last_day_of_week])>dmn_email[Min sent date weekly],"Returning","New")
       
      thanks again for support!