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!
Szokens's avatar
Szokens
Helper II
1 year ago

Hi v-tejrama , your solution is nice however it count rows for "New" or "Returning" from whole dmn_email table & specific C1-C4 column. I forgot to mention my vizual will have additional slicers like:

dmn_email[sent_date],

v_sdp_employee[business_title]

v_sdp_employee[business_area],

dmn_recipient[internal_external].

 

relations:

v_sdp_employee 1 - * dmn_email

dmn_recipient 1 - * dmn_email

This was one thing, second is that I think this disconnected table lost date info to which "New" / "Returning" flag is connected to (so when looking on day by day or month by month ect. like I mentioned initially I could connect these flags with specific dates).

To have more background, C1 - C4 columns are actually date columns from dmn_date table (dmn_date 1 - * dmn_email) as follows:

C1 - is specific calendar date

C2 - is a last day of the week of C1

C3 - is last day of the month of C1

C4 - is last day of the quarter of C1

 

Graph I would like to prepare and change dimnension to daily/weekly/monthly/quarterly dynamically, shall look like this:

 

 

 

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!
  • Szokens's avatar
    Szokens
    Helper II
    1 year ago

    This is great, I have modifiet it a bit and it works perfectly. I will mark this as a solution, but by the way can you give me additional advice on one flexible feature? 

    I would like give to user ability which set of values fall into disconnected table "LegendValues".

    I have updated your below formula so it have two variables:

     

    LegendValues =
    VAR NewReturning = DATATABLE(
       "Flag", STRING,
       {
           {"New"},
           {"Returning"}
       }
    )
    VAR BusinessArea = SELECTCOLUMNS(SUMMARIZECOLUMNS(v_sdp_dim_employee_curr[gcrs_busin_area_name]),"Flag",v_sdp_dim_employee_curr[gcrs_busin_area_name])
    RETURN
    SWITCH(TRUE,
    Parameter_Breakdown_By[Breakdown by]="New vs Returning",NewReturning,
    Parameter_Breakdown_By[Breakdown by]="Business Area",BusinessArea)
     
    I have created also additional disconnected table as following:
    Parameter_Breakdown_By = DATATABLE("Breakdown by",STRING,{{"New vs Returning"},{"Business Area"}})
     
    When after "Return" clause I put only (x)BusinessArea or (x)NewReturning it gives me expected result, but underlined part of "LegendValues" seems doesn't be correct - i got an error "the expression specified in the query is a not valid table expression".
    I would like to have influence on the result of "LegendValues" when switching values of "Breakdown by" in slicer.
    • v-tejrama's avatar
      v-tejrama
      Community Support
      1 year ago

      Hi Szokens ,

      Thank you for the follow up.

       

      Here's a reliable tip for dynamically switching legends or axis in Power BI using slicer selections, such as toggling between “New vs Returning” and “Business Area.” While I initially attempted a DAX measure with SWITCH and SELECTEDVALUE, it resulted in an error since measures can't be used as fields in visuals.

       

      The optimal approach is to use Field Parameters. By creating a parameter with both Status and Business Area columns from the EmployeeData table through Modeling > New Parameter > Fields, Power BI automatically generated a slicer. I then applied this parameter to the X-axis of a Stacked Column Chart, with Total Sales as the Y-axis.

       

      Switching the slicer between Status and Business Area now updates the chart instantly—no need for complex DAX or bookmarks. Field Parameters are a powerful solution for making your visuals more adaptable.

       

      Please find the attached pbix file for your reference.


      Best Regards,
      Tejaswi.
      Community Support