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!