Reply
Regular Visitor
Posts: 15
Registered: ‎12-07-2017
Accepted Solution

Filter data using slicer (1 table to another)

[ Edited ]

Hi 

 

I have a report that I need the end user to be able to categorise a 'holding' on my 'holdings table' as either beef or dairy based on a percent using a visual filter, eg slicer. The filter is based of a percentage, ie, if the holding has 'PERCENT_DAIRY'>= x then categorise these holdings as 'Dairy', else categorise as beef

 

Below is the sample data:

 

HOLDINGID     PERCENT_DAIRY   

Holding_1        60

Holding_2        80

Holding_3        40

Holding_4        20

Holding_5        90

Holding_6        94

 

If the end user uses a filter, eg slicer to say that any holding with 'PERCENT_DAIRY' IS >= 80 then 'dairy' else 'beef'

 

END RESULT:

 

HOLDINGID     PERCENT_DAIRY  CATEGORY 

Holding_1        60                        BEEF

Holding_2        80                        DAIRY

Holding_3        40                        BEEF

Holding_4        20                        BEEF

Holding_5        90                        DAIRY

Holding_6        94                        DAIRY

 

I have the 'holding' table with holding data (see above) and a 'percent' table showing values 1 - 100

 

My question is how do I create a custom category (ie, dairy, beef) based on the percent choosen by the end user 

 

Thank you for any help you can provide.


Accepted Solutions
Member
Posts: 117
Registered: ‎08-01-2017

Re: Filter data using slicer (1 table to another)

Hi @justinMcC,

 

Create 2 Measures to achieve this ac below.

 

Measure 1:

 

Calculate Dairy % = CALCULATE(SUMX(your_table_name, your_table_name[PERCENT_DAIRY]),ALLEXCEPT(your_table_name, your_table_name[HOLDINGID]))

Measure 2:

 

Category = IF([Calculate Dairy %] >= 80, "DAIRY","BEEF")

dataasas.PNG

 

Please give a kudo and Accept the soultion if it works for you.

 

Thanks,

Mehbub- India

View solution in original post


All Replies
Member
Posts: 117
Registered: ‎08-01-2017

Re: Filter data using slicer (1 table to another)

Hi @justinMcC,

 

Create 2 Measures to achieve this ac below.

 

Measure 1:

 

Calculate Dairy % = CALCULATE(SUMX(your_table_name, your_table_name[PERCENT_DAIRY]),ALLEXCEPT(your_table_name, your_table_name[HOLDINGID]))

Measure 2:

 

Category = IF([Calculate Dairy %] >= 80, "DAIRY","BEEF")

dataasas.PNG

 

Please give a kudo and Accept the soultion if it works for you.

 

Thanks,

Mehbub- India

Regular Visitor
Posts: 15
Registered: ‎12-07-2017

Re: Filter data using slicer (1 table to another)

Hi @mehaboob557

 

Thanks for all your help, your solution worked

 

The next step I have however is to create a chart from the results, ie Beef = 4, Dairy = 4, however I cant use a measure is a chart

 

Do you know how to turn a measure into a column?

 

Again thank you for the above, your help was much appreciated

Highlighted
Member
Posts: 117
Registered: ‎08-01-2017

Re: Filter data using slicer (1 table to another)

Show me some output how you need.

 

If possible create a new forum post and send me the link . I will check and if possible i will help

Regular Visitor
Posts: 15
Registered: ‎12-07-2017

Re: Filter data using slicer (1 table to another)

Hi @mehaboob557

 

I created another topic on forum, here is the link:

 

http://community.powerbi.com/t5/Desktop/Turning-a-column-into-a-measure/m-p/331989#M148440

 

Any help you could give would be much appreciated

 

Thanks