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

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