Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

User selectable values from dim table

Hello,
I am implementing a new feature on a report page which allows a user to select which list of values from dim_table01 will then be used to filter three fact tables. The goal here is for the user to pick one of three options, 'sel_01', 'sel_02', or 'sel_all' from some kind of visual, perhaps a button slicer, which filters the dim_table01[col-00] according to "true" values in the corasponding column.  i.e. If the user chose 'sel_01', then values in dim_table01[col-00] would be selected where col-01 = true. 

 

The image is showing the data model layout with:
     2 dim tables, dim_table01 and dim_type (disconnected table)
    3 fact tables, fact_table01, fact_table02 and fact_table03
    an example of user selectable buttons
    expected outcome based on user's selection
    and my logical thought for a solution

I am asking for help on how to achieve the original goal of allowing a user to select which list of values from dim_table01 will then be used to filter three fact tables.

I am open to all ideas.  --- Thanks

 



Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    I made a sample for you.

     

    Measure = SWITCH(
    SELECTEDVALUE('dim_type'[type]),
    "sel_01",
    IF( VALUES( 'dim_table01'[col-01])=TRUE(),CALCULATE( SUM('fact_table01'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table01[fact_c01])),BLANK()),
    "sel_02",
    IF( VALUES( 'dim_table01'[col-02])=TRUE(),CALCULATE( SUM('fact_table02'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table02[fact_c01])),BLANK()),
    "sel_all",
    IF( VALUES( 'dim_table01'[col-all])=TRUE(),CALCULATE( SUM('fact_table03'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table03[fact_c01])),BLANK())
)

     

    Best Regards,

    Wearsky

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Are you trying to reinvent the Field Parameters feature?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I have tried Filed Parameters (FPs). I created a FP which inlcluded col-01, col-02 and col-all. I found the  FP would switch between the columns however, switching the column is not enough to filter  dim_table01 and there by filter the fact_tables.
      I tried replacing the true/false values in the col-01,col-02 & col-all with values from col-00.  True = col-00 valus; False = blank.

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        How about enabling "Personalize Visuals"  and train your users in how to use that?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    I made a sample for you.

     

    Measure = SWITCH(
    SELECTEDVALUE('dim_type'[type]),
    "sel_01",
    IF( VALUES( 'dim_table01'[col-01])=TRUE(),CALCULATE( SUM('fact_table01'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table01[fact_c01])),BLANK()),
    "sel_02",
    IF( VALUES( 'dim_table01'[col-02])=TRUE(),CALCULATE( SUM('fact_table02'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table02[fact_c01])),BLANK()),
    "sel_all",
    IF( VALUES( 'dim_table01'[col-all])=TRUE(),CALCULATE( SUM('fact_table03'[value]),USERELATIONSHIP(dim_table01[col-00],fact_table03[fact_c01])),BLANK())
)

     

    Best Regards,

    Wearsky