Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |