cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rimgri Member
Member

Multiple selection in measure filter

Hello,

I need to have a filter with slice the histogram and shows only those columns that are ticked in the filter.
I used this solution:
https://www.youtube.com/watch?v=gYbGNeYD4OY&list=PLorkQuhD4KtQX0vM3g9ChxDMhHhi7SUOq&index=18&t=0s
But the problem is that it works just with one selection when there is two or more it brings error.
My dax is like this (I don't have measures I have columns):

Measure = IF(ISCROSSFILTERED('Measure Dimensions '[Index]);
SWITCH(TRUE();
VALUES('Measure Dimensions '[Index])=1; LASTNONBLANK('TOC History Period'[Value of Excess inventory over Buffer];0);
VALUES('Measure Dimensions '[Index])=2; LASTNONBLANK('TOC History Period'[Value of Excess inventory over Buffer and restrictions];0);
VALUES('Measure Dimensions '[Index])=3; LASTNONBLANK('TOC History Period'[Inventory value (MAX period day)];0);
VALUES('Measure Dimensions '[Index])=4; LASTNONBLANK('TOC History Period'[Value of Excess inventory over Safe Minimum];0);
BLANK());BLANK())

Maybe you have some advice on how can I correct the dax to meet my needs?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Multiple selection in measure filter

Hi @rimgri ,

 

Don't know how your data looks like but I have made a similar answer on another post, based on that answer and looking at your needs I have recalculated my measure to match yours and achieved the correct result.

 

Have you tried the following measure:

 

Measure =
SWITCH (
    TRUE ();
    VALUES ( 'Measure Dimensions '[Index] ) = 1; LASTNONBLANK ( 'TOC History Period'[Value of Excess inventory over Buffer]; 0 );
    VALUES ( 'Measure Dimensions '[Index] ) = 2; LASTNONBLANK (
        'TOC History Period'[Value of Excess inventory over Buffer and restrictions];
        0
    );
    VALUES ( 'Measure Dimensions '[Index] ) = 3; LASTNONBLANK ( 'TOC History Period'[Inventory value (MAX period day)]; 0 );
    VALUES ( 'Measure Dimensions '[Index] ) = 4; LASTNONBLANK (
        'TOC History Period'[Value of Excess inventory over Safe Minimum];
        0
    );
    BLANK ()
)

Be aware that the LASTNONBLANK will only return a value if your table has more than one value for the X-Axis values it will return different result than expected.

 

If you cannot get the expected behaviour please share a mockup of your file.

 

See attach my file with only two columns but with a measure similar to yours.

 

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Multiple selection in measure filter

Hi @rimgri ,

If I understand your requiement correctly that you want to select Total Sales&&Total Cost or others in the slicer and then show the value in the Stacked column chart.

If so, you could refer to my logic below.

I think you shold use IF function to determine if multiple items have been selected in the slicer firstly, if you select the single item then use SWITCH function as you used currently, if you selecte multiple items, you could calculate with other formulas. 

However, for the logic above, you only could show the total value for the items you selected in the stacked chart. If you want to both show Total sales and Total costs sparately, I'm afraid that you need to add the category which should be a little complex.

 Hope @MFelix , @parry2k will have other idea.

Best  Regards,

Cherry

 

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rimgri Member
Member

Re: Multiple selection in measure filter

Thank's @v-piga-msft for the replay,

 

Yes, if there are selected Total Sales and Total Cost I need them both to show in the chart.

But instead Stacked column chart I use a Line chart.

Super User
Super User

Re: Multiple selection in measure filter

Hi @rimgri ,

 

Don't know how your data looks like but I have made a similar answer on another post, based on that answer and looking at your needs I have recalculated my measure to match yours and achieved the correct result.

 

Have you tried the following measure:

 

Measure =
SWITCH (
    TRUE ();
    VALUES ( 'Measure Dimensions '[Index] ) = 1; LASTNONBLANK ( 'TOC History Period'[Value of Excess inventory over Buffer]; 0 );
    VALUES ( 'Measure Dimensions '[Index] ) = 2; LASTNONBLANK (
        'TOC History Period'[Value of Excess inventory over Buffer and restrictions];
        0
    );
    VALUES ( 'Measure Dimensions '[Index] ) = 3; LASTNONBLANK ( 'TOC History Period'[Inventory value (MAX period day)]; 0 );
    VALUES ( 'Measure Dimensions '[Index] ) = 4; LASTNONBLANK (
        'TOC History Period'[Value of Excess inventory over Safe Minimum];
        0
    );
    BLANK ()
)

Be aware that the LASTNONBLANK will only return a value if your table has more than one value for the X-Axis values it will return different result than expected.

 

If you cannot get the expected behaviour please share a mockup of your file.

 

See attach my file with only two columns but with a measure similar to yours.

 

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 331 members 3,518 guests
Please welcome our newest community members: