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 ,

 

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!