cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Getting value from another table (timeperiod)

I have a measure that takes every unique timeperiod value from a dimension-table and then calculates what value falls in that timeperiod in the fact-table and counts them. I want to create a graph showcasing the timeperiod values on x and the count on y. But the value is off.

The problem is that the X-axis only counts one row on the starting value and not the other periods of time. Does anybody know how to fix this? To illustrate: Starting time is 15:00 and ending time 17:00, it only counts one value at 15:00 instead of counting 1 value at 16:00 and 1 at 17:00. The measure counts the total correct, but the problem occurs when trying to showcase it on a graph

1 ACCEPTED SOLUTION
Community Support

For your question, here is the method I provided:

Here's some dummy data

"dim"

"fact"

Create a measure. Counts the values in the fact table for that time period.

``````count num =
var _dimtime = SELECTEDVALUE('dim'[Time])
var _facttime = SELECTEDVALUE('fact'[Time])
RETURN
IF(
_dimtime = _facttime,
CALCULATE(COUNTROWS('fact'), FILTER('fact', 'fact'[Time] = MAX('fact'[Time]))),
0
)``````

Here is the result.

Regards,

Nono Chen

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

2 REPLIES 2
Super User

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result in a Table format.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

For your question, here is the method I provided:

Here's some dummy data

"dim"

"fact"

Create a measure. Counts the values in the fact table for that time period.

``````count num =
var _dimtime = SELECTEDVALUE('dim'[Time])
var _facttime = SELECTEDVALUE('fact'[Time])
RETURN
IF(
_dimtime = _facttime,
CALCULATE(COUNTROWS('fact'), FILTER('fact', 'fact'[Time] = MAX('fact'[Time]))),
0
)``````

Here is the result.

Regards,

Nono Chen

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors