cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErickReiis
Frequent Visitor

Filter using a range of values in buttons

 

We are developing a project for a pharmaceutical company, and a screen that shows the pharmacies, and the amount of sales they obtained in a specified period in the date range of two segmentations (Initial Period - Final Period)

 

Sales Varied = Amount of Sales in period B / Amount of Sales in period A.

 

Through the period of analysis, we made a measure that calculates how much those sales varied between these selected periods:

 

Imagem 3.jpg

 

I want to put some buttons:

Imagem 4.png

That's where our buttons would come in. Let's say the person wants to see the pharmacies that had a variation greater than 10%. For this, she would click on the 10% button positioned below and it would filter all pharmacies with a variation greater than 10%.

 

And the same rule would apply to the other buttons, the 5% would show the pharmacies that had a variation between 5% and 10%

 

the 1% would show the pharmacies that had a variation between 5% and 1%

 

the 0% would show the pharmacies that had a variation between 1% and 0%

 

And so for others.

 

How can I apply this range rule to filter through buttons?

 

I tried this measure but the result is always blank:

 

Medida chave =
SWITCH (
TRUE ();
'Dim Seletor Variação'[Selecionar Medida] = "10,00%"; [Top 25 PDV 10 %];
'Dim Seletor Variação'[Selecionar Medida] = "5,00%"; [Top 25 PDV 5 %];
'Dim Seletor Variação'[Selecionar Medida] = "1,00%"; [Top 25 PDV 10 %];
'Dim Seletor Variação'[Selecionar Medida] = "0,00%"; [Top 25 PDV 10 %];
'Dim Seletor Variação'[Selecionar Medida] = "-1,00%"; [Top 25 PDV 10 %];
'Dim Seletor Variação'[Selecionar Medida] = "-5,00%"; [Top 25 PDV 10 %];
'Dim Seletor Variação'[Selecionar Medida] = "-10,00%"; [Top 25 PDV 10 %];
 
[TOP 25 PDV]
)

 

And i tried this one for the 10% button:

Top 25 PDV 10 % =
CALCULATE (
[Unidades vendidas por PDV];
TOPN ( 25; ALL ( 'Dim PDV'[Fantasia + CNPJ] ); [Sales Amount] );
VALUES ( 'Dim PDV'[Fantasia + CNPJ] );
FILTER (
'Sales';
[% Variation A/B] >= 0,1))
 
But it doesnt work too 😞 
 

Can you help me ?

 

 
1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @ErickReiis ,

"the 5% would show the pharmacies that had a variation between 5% and 10%", in your formula, would only output the result "Dim Seletor Variação'[Selecionar Medida] = 50%" instead between 5%and 10%.

 

If you want to output result between the values, please have a try.

Medida chave =
SWITCH (
    TRUE ();
    'Dim Seletor Variação'[Selecionar Medida] >= 0.1; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0.05&&'Dim Seletor Variação'[Selecionar Medida] < 0.1; [Top 25 PDV 5 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0.01&&'Dim Seletor Variação'[Selecionar Medida] < 0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0&&'Dim Seletor Variação'[Selecionar Medida] < 0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= -0.05&&'Dim Seletor Variação'[Selecionar Medida] <-0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >=-0.1&& 'Dim Seletor Variação'[Selecionar Medida] < -0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] < -0.1; [Top 25 PDV 10 %];
    [TOP 25 PDV]
)

 

If you only want to modify the answer, only need to check the type of the [Selecionar Medida]! If it is the whole number or other number type, then you do not need to use double quotes.double quotes just so happens to be the string with which your "text" string should be replaced.

Medida chave =
SWITCH (
    TRUE ();
    'Dim Seletor Variação'[Selecionar Medida] = 0.1; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] =0.05; [Top 25 PDV 5 %];
    'Dim Seletor Variação'[Selecionar Medida] = 0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = 0; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = -0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = -0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] =-0.1; [Top 25 PDV 10 %];
    [TOP 25 PDV]
)

 Please have a try.

Top 25 PDV 10 % =
CALCULATE (
    [Unidades vendidas por PDV];
    TOPN ( 25; ALL ( 'Dim PDV'[Fantasia + CNPJ] ); [Sales Amount] );
    VALUES ( 'Dim PDV'[Fantasia + CNPJ] );
    FILTER ( ALLSELECTED ( 'Sales' ); [% Variation A/B] >= 0,1 )
)

 

If it doesn't work, please provide some sample data without privacy information and desired output.

 

How to Get Your Question Answered Quickly 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-polly-msft
Community Support
Community Support

Hi @ErickReiis ,

"the 5% would show the pharmacies that had a variation between 5% and 10%", in your formula, would only output the result "Dim Seletor Variação'[Selecionar Medida] = 50%" instead between 5%and 10%.

 

If you want to output result between the values, please have a try.

Medida chave =
SWITCH (
    TRUE ();
    'Dim Seletor Variação'[Selecionar Medida] >= 0.1; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0.05&&'Dim Seletor Variação'[Selecionar Medida] < 0.1; [Top 25 PDV 5 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0.01&&'Dim Seletor Variação'[Selecionar Medida] < 0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= 0&&'Dim Seletor Variação'[Selecionar Medida] < 0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >= -0.05&&'Dim Seletor Variação'[Selecionar Medida] <-0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] >=-0.1&& 'Dim Seletor Variação'[Selecionar Medida] < -0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] < -0.1; [Top 25 PDV 10 %];
    [TOP 25 PDV]
)

 

If you only want to modify the answer, only need to check the type of the [Selecionar Medida]! If it is the whole number or other number type, then you do not need to use double quotes.double quotes just so happens to be the string with which your "text" string should be replaced.

Medida chave =
SWITCH (
    TRUE ();
    'Dim Seletor Variação'[Selecionar Medida] = 0.1; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] =0.05; [Top 25 PDV 5 %];
    'Dim Seletor Variação'[Selecionar Medida] = 0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = 0; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = -0.01; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] = -0.05; [Top 25 PDV 10 %];
    'Dim Seletor Variação'[Selecionar Medida] =-0.1; [Top 25 PDV 10 %];
    [TOP 25 PDV]
)

 Please have a try.

Top 25 PDV 10 % =
CALCULATE (
    [Unidades vendidas por PDV];
    TOPN ( 25; ALL ( 'Dim PDV'[Fantasia + CNPJ] ); [Sales Amount] );
    VALUES ( 'Dim PDV'[Fantasia + CNPJ] );
    FILTER ( ALLSELECTED ( 'Sales' ); [% Variation A/B] >= 0,1 )
)

 

If it doesn't work, please provide some sample data without privacy information and desired output.

 

How to Get Your Question Answered Quickly 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.