How to reference a query (table)

Question

Hi,I have the following query which is also a table. I am thinking to use CALCULATE as a measure in Power BI report, but I don't know how to reference different row in the query by situation?&nbsp;For example, when I create a measure called "2025 control budget", I would like combine two set of data, Actual cost data will come from a query which any cost before and include 2024 Dec data, then forecast cost data will come another query which will include cost info from Jan 2025 and onward. I plan to create four measures as listed under Name column.Can you please provide me some suggestions? Thanks,

rajendraongole1 · Answer

Hi&nbsp;Anonymous&nbsp; -&nbsp;you can structure your measure for "2025 Control Budget"
&nbsp;
2025 Control Budget =VAR SelectedForecast = SELECTEDVALUE( 'YourTable'[Forecast] )RETURNCALCULATE(SUM( 'ActualCostTable'[Cost] ),'ActualCostTable'[Date] &lt;= DATE(2024,12,31)) +CALCULATE(SUM( 'ForecastCostTable'[Cost] ),'ForecastCostTable'[Date] &gt;= DATE(2025,1,1))
&nbsp;
replace with your table name,&nbsp;or the other measures (Prev Month -1 Forecast HCC, Prev Month Forecast HCC, Working Forecast), you can create similar measures but adjust the date filters accordingly.
&nbsp;
Hope this helps.

anonymous · Answer

HI&nbsp;Anonymous,
You can put this part into variable and use if statement or switch function to handle and return different results based on calculation groups.
Using calculation groups to selectively replace measures in DAX expressions - SQLBI
Create calculation groups in Power BI - Power BI | Microsoft Learn
Regards,
Xiaoxin Sheng

anonymous · Answer

Hi, rajendraongole1:&nbsp;Thanks for your help. Appreciated!I am wondering if the highlighted yellow dates can be directly referenced to the query above from different rows, which I shared earlier based on which measure I set up.&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi,&nbsp;Anonymous:Thanks for your reply and help. Appreciated! Would you mind helping me further?&nbsp;My situation is like this:I have the following three queries for which, one side has the actual up to date cost data, and the other has forecast and budget cost data. Both data sets has similar structure.Both Actual and Budget forecast queries have the following similar content.Then I have the third query which was shared earlier.&nbsp;Eventually, I would like to have a report which has a field parameter of 2025 control budget, working forecast, prev month's forecast or prev prev month's forecast. And the report will be a matrix with funding code as row header, and time as column header.I was thinking about using CALCULATE as discussed earlier. would you mind giving me more directions on how? or is there any other methods to achieve the same result? please let me know. Thanks, &nbsp;&nbsp;

anonymous · Answer

HI&nbsp;Anonymous,
So you mean you just want to summary records from two table based on current column values?If that's the case, you can add calculated columns to use current Name and date to look up correspond table records.
Actual =
CALCULATE (
    SUM ( ActualTable[amount] ),
    FILTER (
        ActualTable,
        [version_name] = EARLIER ( SummaryTable[name] )
            &amp;&amp; [month_number] = EARLIER ( SummaryTable[actual] )
    )
)

Forecast =
CALCULATE (
    SUM ( Forecase[amount] ),
    FILTER (
        ActualTable,
        [version_name] = EARLIER ( SummaryTable[name] )
            &amp;&amp; [month_number] = EARLIER ( SummaryTable[forecast] )
    )
)
Regards,
Xiaoxin Sheng

Forum Discussion

How to reference a query (table)

6 Replies

Related content

UDF with variable table reference and fixed column reference

Calculated table refers to remote table via Direct Query

Query Folding and Query References

Table Query

use cases of duplicate and reference in power query