Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Counting Instances of an ID from another table

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Counting Instances of an ID from another table

12-12-2018
10:54 AM

Hey All,

I have two tables. One includes headcount information, and the other includes records of classes taken by the people in the headcount table. I want to created a visual that shows what percentage of employees have completed 25%, 50%, 75%, or 100% of available classes.

I am trying to figure out if I should do this in a column or a measure. However I am fairly new to Power BI and can;t think of a formula for either. I am thinking I would need to do it in a column so that i could then perform a count of employees that completed a certain number of classes.

Does anyone have thoughts on what the correct way to do this would be?

Thanks

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-12-2018
11:11 PM

hi, @Scoobyrooba

First, you should know difference between measure and column, here are two blogs for you learn:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Even if they look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

At this point, you might be wondering when to use calculated columns over measures. Sometimes either is an option, but in most situations your computation needs determine your choice.

You have to define a calculated column whenever you want to do the following:

Place the calculated results in a slicer, or see results in rows or columns in a pivot table (as opposed to the values area), or in the axes of a chart, or use the result as a filter condition in a DAX query.

Define an expression that is strictly bound to the current row. For example, Price * Quantity cannot work on an average or on a sum of the two columns.

Categorize text or numbers. For example, a range of values for a measure, a range of ages of customers, such as 0–18, 18–25, and so on.

However, you must define a measure whenever you want to display resulting calculation values that reflect user selections and see them in the values area of a pivot table, or in the plot area of a chart – for example:

When you calculate profit percentage on a certain selection of data.

When you calculate ratios of a product compared to all products but keeping the filter both by year and region.

So you still have the problem, you could share some sample data and expected output. and then I can give you more suggestion. **Do mask sensitive data before uploading.**

Best Regards,

Lin

Community Support Team _ Lin

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

If this post

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-12-2018
11:11 PM

hi, @Scoobyrooba

First, you should know difference between measure and column, here are two blogs for you learn:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Even if they look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

At this point, you might be wondering when to use calculated columns over measures. Sometimes either is an option, but in most situations your computation needs determine your choice.

You have to define a calculated column whenever you want to do the following:

Place the calculated results in a slicer, or see results in rows or columns in a pivot table (as opposed to the values area), or in the axes of a chart, or use the result as a filter condition in a DAX query.

Define an expression that is strictly bound to the current row. For example, Price * Quantity cannot work on an average or on a sum of the two columns.

Categorize text or numbers. For example, a range of values for a measure, a range of ages of customers, such as 0–18, 18–25, and so on.

However, you must define a measure whenever you want to display resulting calculation values that reflect user selections and see them in the values area of a pivot table, or in the plot area of a chart – for example:

When you calculate profit percentage on a certain selection of data.

When you calculate ratios of a product compared to all products but keeping the filter both by year and region.

So you still have the problem, you could share some sample data and expected output. and then I can give you more suggestion. **Do mask sensitive data before uploading.**

Best Regards,

Lin

Community Support Team _ Lin

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

If this post

Featured Topics

Top Solution Authors

User | Count |
---|---|

206 | |

84 | |

82 | |

77 | |

48 |

Top Kudoed Authors

User | Count |
---|---|

165 | |

87 | |

85 | |

80 | |

74 |