Filter matrix based on a slicer which is based on a measure

[sandy_PBI]

hi, i have a matrix visual which contains the current month demand, current_month + 1 demand, difference between the 2 in absolute quantities and percentage difference. All measures in this table are switch measures so that the user can use to switch between quantity and value. 
to avoid infinity, i have capped the %age to 999. 

DynamicDemandChange_ABS_% =

VAR SelectedMeasure =

    SWITCH(

        [SelectedOption],

        "Units", [DEMAND_CHANGE_%_ABS],

        "$", [DEMAND_CHANGE_%_ABS_DOLLARS]

    )

RETURN

    IF(

        SelectedMeasure > 9  || NOT(ISNUMBER(SelectedMeasure)),

        9.99,

        SelectedMeasure

    )
The requirement is to provide a slicer where the user can slice between 0 and 999 to review differences as filtere by this slicer. 
I have tried the following :

1. percentage_sliver : create a table using GENERATESERIES(0,9.99,0.1). I tried using max(DynamicDemandChange_ABS_%) instead of 9.99 but it did not generate the series. Hence i hard coded the min-max to 0 and 9.99. Ideally i would want to replace 9.99 with max(DynamicDemandChange_ABS_%) but it is not working.
would like for the below to work but is only giving 0, 0.01, 0.02 in the series. i know that i have %age upto 999%

LAG1_FILTER =

VAR MaxValue = CALCULATE([DEMAND_CHANGE_%_ABS_LAG1], ALL())

RETURN

GENERATESERIES(0, MaxValue, 0.01)

Hence i changed the above code to 

LAG1_FILTER =

VAR MaxValue = CALCULATE([DEMAND_CHANGE_%_ABS_LAG1], ALL())

RETURN

GENERATESERIES(0, 9.99, 0.01)

2. Next step i created a measure 

Lag1_filter =

VAR MIN_VALUE = 0

VAR MAX_VALUE = 999

VAR CURRENTMEASUREVALUE = [DEMAND_CHANGE_%_ABS]

RETURN

    IF (

        CURRENTMEASUREVALUE >= MIN_VALUE && CURRENTMEASUREVALUE <= MAX_VALUE,

        1,

        0

    )

 i have added a IF condition to check the slicer is working. but wven when slide the slicer to contain the range or elimiate some range of percentages, the measure is not filtered.

how do I get my slicer working? when i remove the cap of 9.99, then the only value for which the slicer is working is infinity. 

appreciate any pointers. 

thanks.

[Anonymous]

Hi,TomMartens ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@sandy_PBI .I am glad to help you.

I give the following advice from your description

For your question

1. percentage_sliver : create a table using GENERATESERIES(0,9.99,0.1). I tried using max(DynamicDemandChange_ABS_%) instead of 9.99 but it did not generate the series. Hence i hard coded the min-max to 0 and 9.99. Ideally i would want to replace 9.99 with max(DynamicDemandChange_ABS_%) but it is not working.

The values in measure cannot be passed in real time to the calculate column used for slicer fields.
The essential difference that exists between measure and calculate column is:
measure generates dynamically changing scalar values (not stored in the data model) based on the current computational environment (filter conditions)

On the other hand, calculate columns (including calculate tables), which are generally grouped from different dimensions of the data, are real attributes.
(exist in the data model, occupy memory)
And the value of calculate column will not change after creation/manual refresh, even if their values are from other measures.

I hope you find the following article helpful, try “putting calculated columns created based on measure into the axes of the chart” and “putting calculated columns into slicer” are both similar ideas.
URL:

The workaround of using the measure as axis in the... - Microsoft Fabric Community

If you want to achieve a similar effect, I think it would be better to just modify the measure and let the result of the measure filter your table instead of relying on the slicer.

like this.
1. Add a parameter that can be chosen or entered by the user, for example, enter a maximum value, and then your measure1[M1] depends on this parameter.
The table visual is then filtered by this measure1[M1].
You can create a second measure2[M2], and make the following judgment:
If the values in the table visual meet the screening range of [M1], then set the value of [M2] to 1, if not, the value of [M2] is set to 0 (through the IF function for judgment).

Then add [M2] as a filter condition to the filter area of table visual.

Here is a simple case of creating parameters to filter the data using the measure itself rather than the slicer. (The slicer field needs to be a static calculated column)

If your slicer itself is not a between type, you can also try to use measure to filter Slicer (since slicer itself is a viusal), but according to the provided screenshot, your slicer is a between type, and you need to provide a specific data interval. So I think it would be better to use the measure itself as a filter.

Regarding the pbix case data, could you please create some test data that can reproduce your problem, (it doesn't need to contain your client's real data, just similar test data)
And share your pbix test files by way of OneDrive/github links, this helps other users to quickly understand your real problem and your computing environment.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sandy_PBI]

Thank @Anonymous for your reply.

this is what i was planning to do as well. to add a conditon to see the selected / current record is within the seelcted range of the slicer then 1 else it should be 0. and then use that as a filter. 
but my logic is failing since the slicer is not recognising the max and min of the measure even if i put ALL to remove all filter conditions. 

i have uploaded the file here : https://drive.google.com/file/d/1wFnjWuL4bnWQAguIhRiAIrQNAWyM5ghv/view?usp=drive_link

could you please review and let me know if am missing anything? or is there any alternative way to approach this? 

thanks.

[Anonymous]

Hi,@sandy_PBI .I am glad to help you.

Unfortunately I can't open the file link properly due to my work environment, you might  to try the github link, looking forward to your reply.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[sandy_PBI]

Hello Carson. @Anonymous 

please check if you can access the file from here.

bugdeep/PBIX_file 

[sandy_PBI]

Hello @Anonymous were you able to review the attachment? thanks

[TomMartens]

Hey @sandy_PBI ,

prepare a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the pbix to OneDrive, Google Drive, or Dropox and share the link.

Regards,

Tom

[sandy_PBI]

Hello Tom,

the data is confidential to the customer and hence i cannot upload the PBIX. i am trying to follow the steps here. but the major difference shown in the video and my approach is that i am using calculated measures as you see in my original post and the measure is percentage. 

do you have any pointers that i should look at without exposing data in a public forum?

https://www.youtube.com/watch?v=AZAL-QPn5Zc&t=372s

[TomMartens]

Hey @sandy_PBI ,

this is precisely why I asked for a pbix containing sample data. Without the semantic model and sample data, it is very time-consuming and error-prone (due to misunderstandings) to provide guidance, not to mention a solution.

Regards,

Tom

[sandy_PBI]

Hello Tom,

i have shared the PBIX here. please let me know if this helps guide me,

https://drive.google.com/file/d/1wFnjWuL4bnWQAguIhRiAIrQNAWyM5ghv/view?usp=sharing