Forum Discussion

vbourbeau's avatar
vbourbeau
Icon for Resolver II rankResolver II
7 years ago

Use selectedvalue in a column

Hi, I want to use a slicer for a sample table who are not linked to any other one.  EX: table 1 Column1 Choice 1 Choice 2 Choice 3   In an other table I want to create a column who change dep...
Need Help
vbourbeau's avatar
vbourbeau
Icon for Resolver II rankResolver II
7 years ago

Ok look this example, it's what I'm tying to do. But in my real data I have more table to filter. So if I click on "last week" both table will filter for the last week result. I don't want to create new amount measure on each table because in reality I have 10 tables. 

What I've trying it's create a column in the Date dimension who change value depending the slicer choice and add a page filter for that column.

I put my example here https://we.tl/t-Q6hNDlJyXq

 

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi vbourbeau ,

 

Looking at your model since you have the tables connect trough date table create the following measure:

 

Filter Dates =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( 'Time'[Sequence] ) = 1; FILTER (
        SUMMARIZE ( Dates; Dates[Date] );
        Dates[Date] <= TODAY ()
            && Dates[Date]
                >= TODAY () - 7
    );
    SELECTEDVALUE ( 'Time'[Sequence] ) = 2; FILTER (
        SUMMARIZE ( Dates; Dates[Date] );
        Dates[Date] <= TODAY ()
            && Dates[Date]
                >= TODAY () - 84
    );
    SELECTEDVALUE ( 'Time'[Sequence] ) = 3; FILTER (
        SUMMARIZE ( Dates; Dates[Date] );
        Dates[Date] <= TODAY ()
            && Dates[Date]
                >= TODAY () - 365
    )
)

Then add this as a filter on your tables and select the option not blank final result will be the following:

 

 

Check the PBIX file attach.

 

Regards,

MFelix

  • vbourbeau's avatar
    vbourbeau
    Icon for Resolver II rankResolver II
    7 years ago

    Hi,

     

    When I put the same logic in my full report I've got this message:

     

    "A table of multiple values was supplied where a single value was expected"

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago
      Can you share the measure you há é created?

      Regards,
      MFelix
      • vbourbeau's avatar
        vbourbeau
        Icon for Resolver II rankResolver II
        7 years ago
        Filter Dates = 
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Temps[Sequence] ) = 1; FILTER (
        SUMMARIZE ( DatesFull;DatesFull[Dates] );
        DatesFull[Dates] <= TODAY ()
            && DatesFull[Dates]
                >= TODAY () - 7
    );
    SELECTEDVALUE ( Temps[Sequence] ) = 2; FILTER (
        SUMMARIZE ( DatesFull;DatesFull[Dates] );
        DatesFull[Dates] <= TODAY ()
            && DatesFull[Dates]
                >= TODAY () - 84
    );
    SELECTEDVALUE ( Temps[Sequence] ) = 3; FILTER (
        SUMMARIZE ( DatesFull;DatesFull[Dates] );
        DatesFull[Dates] <= TODAY ()
            && DatesFull[Dates]
                >= TODAY () - 365
    )
)