Getting value from another table (timeperiod)

[powerbilover123]

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

[Ashish_Mathur]

Hi,

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

[v-nuoc-msft · Accepted solution]

Hi @powerbilover123 

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.