Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

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.
Anonymous
Not applicable

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.

Hi @Anonymous ,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.