The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
the intention is to obtain the firstnonblankvalue of the quarter. Why is the filter condition with parallelperiod necessary here??
Would it be okay if i remove the calculatetable as in the following code.
VAR FirstDateInQuarter =
FIRSTNONBLANK (
'Date'[Date],
COUNTROWS ( RELATEDTABLE( MSFT ) )
)
VAR Result =
CALCULATE (
AVERAGE ( MSFT[Value] ),
FirstDateInQuarter
)
RETURN
Result
Solved! Go to Solution.
In the original SOQ measure, PARALLELPERIOD creates a date filter by taking the set of visible dates from the original filter context and expanding those to complete calendar quarters spanning the minimum to maximum visible date.
The intention is to return the average value on the date returned by FIRSTNONBLANK within the quarter(s), regardless of the "location" of the current date filter within the quarter(s).
For example:
In your suggested measure, without CALCULATETABLE/PARALLELPERIOD, FIRSTNONBLANK would operate within the existing filter context, and the result would be the first date on which the expression is nonblank within the existing filter context, which in this case would be the month, quarter, or year depending on the location in the matrix.
You can test out both measures in a visual and compare the difference.
Hi @Cyriackpazhe ,
Thanks for reaching out to Microsoft Fabric Community,
Just wanted to check if the solution provided by @OwenAuger has met your needs. If yes, Please consider marking it as "Accepted Solution" to assist others with similar queries. If further assistance is needed, please reach out.
Thank you.
Thanks
In the original SOQ measure, PARALLELPERIOD creates a date filter by taking the set of visible dates from the original filter context and expanding those to complete calendar quarters spanning the minimum to maximum visible date.
The intention is to return the average value on the date returned by FIRSTNONBLANK within the quarter(s), regardless of the "location" of the current date filter within the quarter(s).
For example:
In your suggested measure, without CALCULATETABLE/PARALLELPERIOD, FIRSTNONBLANK would operate within the existing filter context, and the result would be the first date on which the expression is nonblank within the existing filter context, which in this case would be the month, quarter, or year depending on the location in the matrix.
You can test out both measures in a visual and compare the difference.
User | Count |
---|---|
17 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
9 | |
8 |