Forum Discussion
Mapping suppliers with different categories
Hi,
I have a long list of suppliers with latitude and longitude and a category (Milk, Honey, Bread etc). The data is from an old Excel database where each supplier can be on multiple rows if they supply to different categories. For example Supplier X can supply both Milk and Honey and will have two records (rows). I want to map ONLY suppliers delivering both Milk and Honey. How do I do this?
KR,
M
I would proberbly make a calculated column in the dataset defining if the row is related to a company supplying both honey and milk.
Company Produce Category Company 1 Honey Milk/Honey Company 1 Milk Milk/Honey Company 2 Honey N/A Company 2 Wheat N/A Company 3 Honey Milk/Honey Company 3 Milk Milk/Honey Company 4 Bread N/A
The calculated column would proberbly look somehting like this:Column = VAR Company = *Table'[Company] Return IF( AND( CALCULATE( COUNTROWS( 'Table' ), ALL('Table'), 'Table'[Company] = Company, 'Table'[Produce] = "Honey" ) > 0 , CALCULATE( COUNTROWS( 'Table' ), ALL('Table'), 'Table'[Company] = Company, 'Table'[Produce] = "Milk" ) > 0 ), "Milk/Honey", "N/A" )
4 Replies
- tex628
Community Champion
What exactly do you mean by map here? Could you show the desired outcome somehow?
I want to create a map where I only show a marker where a company deliveres both Milk and Honey (in the attached case the yellow). My problem is that a record occurs twice (two separate rows in excel) for for the yello company, once for Milk and once for Honey. In the attached example it shows all companies delivering either milk or honey
- tex628
I would proberbly make a calculated column in the dataset defining if the row is related to a company supplying both honey and milk.
Company Produce Category Company 1 Honey Milk/Honey Company 1 Milk Milk/Honey Company 2 Honey N/A Company 2 Wheat N/A Company 3 Honey Milk/Honey Company 3 Milk Milk/Honey Company 4 Bread N/A
The calculated column would proberbly look somehting like this:Column = VAR Company = *Table'[Company] Return IF( AND( CALCULATE( COUNTROWS( 'Table' ), ALL('Table'), 'Table'[Company] = Company, 'Table'[Produce] = "Honey" ) > 0 , CALCULATE( COUNTROWS( 'Table' ), ALL('Table'), 'Table'[Company] = Company, 'Table'[Produce] = "Milk" ) > 0 ), "Milk/Honey", "N/A" )
