cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Separating Data

I've started out using B.I recently and I'm having issues with a situation that happens often at my workplace.

 

I have a data set about constructions that often involves more than one city. As an example with random data:

 

RoadCityLength (miles)TypeCategoryValue ($)
John St.A10RoadWide1000
Mary St.B20InterstateWide2000
Tucson St.C/D40RoadNarrow4000

 

I have 3 roads I'm working on 4 different cities, since Tucson St. runs across two cities, so I've tried separating the data into:

 

RoadCityLength (miles)TypeCategoryValue ($)

John St.

A10RoadNarrow1000
Mary St.B20InterstateWide2000
Tucson St.C10RoadNarrow1000
Tucson St.D30RoadNarrow3000

 

I can do DISTINCTCOUNT in order to get the amount of Roads or Cities I'm working with to present my data and I can divide both my Length and Value between the cities, but I have a problem with other colums such as "Type" and "Category", of which I have several with lots of details. If I have any sort of visual that has those options, the total amount is higher than what it actually is. For this example, it'd say I have 3 Roads and 1 Interstate, when I'm only working with 2 Roads and 1 Interstate (same with 3 narrows vs. 2 narrows). How can I divide my data or maybe work on it in order not to count those again?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Separating Data

@cwolff 

You will have to add additional columns in the table to get distinct of those.

For Example:

 

Key = COMBINEVALUES("-",[Road], [City],[Type],[Category])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn





View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Separating Data

@cwolff 

You will have to add additional columns in the table to get distinct of those.

For Example:

 

Key = COMBINEVALUES("-",[Road], [City],[Type],[Category])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn





View solution in original post

Highlighted
Regular Visitor

Re: Separating Data

Sorry, I don't think I get it, isn't COMBINEVALUES only for measures? Also, should I add columns to my original table?

Highlighted
Community Champion
Community Champion

Re: Separating Data

@cwolff 

You can add as a column in a table.

Example

Fowmy_0-1601323892316.png



________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Highlighted
Regular Visitor

Re: Separating Data

Thank you very much, this was perfect!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors