This guide builds a Power BI semantic model on Fokals tables for hiring and technology trends and for the weekly market series. It sets out which tables are facts, which measures cannot be summed, how refresh and incremental refresh fit tables that are written once, and how row-level security keeps each viewer inside the scope your licence allows.
Fokals is delivered direct, by REST API and as bulk files in JSON, JSON Lines or CSV, which you load into a lakehouse, warehouse or database that Power BI reads, or import with Power BI's own loaders. Bringing external company data into Microsoft Fabric covers the loading, and this guide starts from the tables.
The model
Microsoft's star schema guidance sorts model tables into dimensions, which filter and group, and facts, which are summarised, joined by one-to-many relationships, with a date table among the dimensions. Fokals tables map onto it without reshaping.
| Model table | Built from | Grain | Related to |
|---|---|---|---|
Company (dimension) | company_id, company, ticker, isin and the other identifiers that every Fokals table carries | One row per company | Every company-level fact |
Date (dimension) | Your own calendar table | One row per day | Each fact, on its date column |
Hiring daily (fact) | Hiring Activity (company_hiring_daily), from the hiring dataset | Company and closed UTC day | Company and Date |
Tech events (fact) | Technology Changes (company_tech_events), from the marketing stack dataset | One dated change on one website | Company and Date |
Market series (fact) | Market Series (market_series), from the market series dataset | Metric, dimension, window and as-of Sunday | Date, on as_of |
The Market series fact has no company key. Its rows describe industries, countries, size bands and markets, computed on same-store cohorts, so it relates to Date and to nothing else. The column names are in the data dictionary.
Measures that add up
Three hiring columns look alike and behave differently. new_postings and closed_postings count events on a day, so they add over days, and net new postings are the first minus the second. open_postings is a level at the end of a day, so adding it over a month counts the same posting thirty times. median_salary_usd is a median, so it cannot be averaged across companies. Write the measures once so that no report author has to remember:
New 30 days =
CALCULATE (
SUM ( 'Hiring daily'[new_postings] ),
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -30, DAY )
)
New previous 30 days =
CALCULATE (
SUM ( 'Hiring daily'[new_postings] ),
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ) - 30, -30, DAY )
)
Hiring momentum =
DIVIDE ( [New 30 days] - [New previous 30 days], [New previous 30 days] )
Open postings latest =
SUMX (
VALUES ( 'Company'[company_id] ),
CALCULATE (
SUM ( 'Hiring daily'[open_postings] ),
LASTDATE ( 'Hiring daily'[day] )
)
)
Tools added =
CALCULATE (
COUNTROWS ( 'Tech events' ),
'Tech events'[category] = "technology",
'Tech events'[change] = "added"
)Hiring momentum compares a company's new postings in the last 30 days with the 30 days before, the construction that growth uses in Market Series, so a company can be read against its industry on one scale. The latest-per-company pattern takes each company's last row in the period and then sums, so a company with no row on the final day is not counted as zero. Take Acme Robotics, an illustrative company with 40 open postings on every day of a 30 day month: a plain sum of open_postings reports 1,200, and Open postings latest reports 40. Tools added counts the Technology Changes rows whose category is technology and whose change is added. A website's first observation is a baseline and writes no event, so the count never includes tools already in place.
For market trends, Market Series rows are already rates over same-store cohorts, and the 7, 30 and 180 day windows overlap, so rows must never be added together. Filter every visual to one metric, one dimension and one window, and return the single value. The unit column names the unit of the rate, such as per 100 companies, a share of postings or a median in US dollars, and for plain counts the rate is empty and the count is the figure. growth is the change against the previous window on the same cohort, index is 100 at the series' first as-of date, and a metric such as tech_added:salesforce can be sliced by industry. A first report page needs three things: a card for Hiring momentum, a line chart of Series rate against as_of for the company's industry, and a table of companies ranked by New 30 days with Open postings latest beside it.
Series rate =
IF (
HASONEVALUE ( 'Market series'[metric] )
&& HASONEVALUE ( 'Market series'[dimension_kind] )
&& HASONEVALUE ( 'Market series'[dimension] )
&& HASONEVALUE ( 'Market series'[window_days] ),
MAX ( 'Market series'[rate] )
)Refresh
In Import mode a semantic model stores a snapshot of the data, and you refresh it to get the latest, according to Microsoft's storage mode page. Microsoft's refresh page limits scheduled refresh to eight times a day on shared capacity and 48 on Premium, Premium per user or Fabric capacity. Fokals writes each daily table once after the day closes and each weekly table once after the week closes, so one refresh after your own load covers the daily tables, and the series need one a week.
If the tables are Delta tables in a Fabric lakehouse or warehouse, a Direct Lake semantic model reads them without importing a copy, and its refresh updates metadata only, which Microsoft says can take a few seconds. It needs a Fabric capacity, where Import works with any Fabric or Power BI licence. Choose by the capacity you hold. Direct Lake tables do not support complex Delta column types, so keep JSON cells such as by_function as text, or expand them into a table of their own before the model reads them.
Incremental refresh suits write-once tables. In Desktop you create RangeStart and RangeEnd date and time parameters, filter the table's date column with them and set how much history to keep and how many recent days to refresh. The service then creates and rolls the partitions, as Microsoft's incremental refresh page describes. The window must still allow for lateness: a period written more than seven days after it closed carries reconstructed=true and can arrive outside a short window. Stamp every row with a loaded_at column when you load it and use that column for Detect data changes, which Microsoft says should differ from the column used for the range. Only the days whose loaded_at moved are then refreshed. Fokals days are closed UTC days, and Microsoft says the service takes the current date and time in UTC unless you set another time zone for the refresh. Leave it on UTC, and the Only refresh complete days option then matches Fokals days.
Row-level security for licence scope
Row-level security filters the rows a viewer sees with DAX rules in roles. Microsoft's page states what it covers. It restricts users in the Viewer role of the workspace, not Admins, Members or Contributors, and it still applies to Viewers who have Build permission. It filters rows and not columns, so column limits need object-level security. A user in several roles gets the union of them, and you assign members to roles in the Power BI service, not in Desktop.
If your agreement restricts use to named teams or to a list of companies, enforce that in the model and not by convention. Load a scope table from your entitlement system with one row for each user and company, and filter the Company table with a dynamic rule:
'Company'[company_id]
IN CALCULATETABLE (
VALUES ( 'User scope'[company_id] ),
'User scope'[user_email] = USERPRINCIPALNAME ()
)Microsoft shows dynamic rules built on USERPRINCIPALNAME() and recommends storing the same identifier format that the function returns. Check what it returns for guest users before you share outside your organisation.
Three limits matter. The rule on Company does nothing to Market series, which has no company key. Its rows are built over cohorts of companies, so decide separately whether a role may see them. Publish to web is not for licensed data: Microsoft's page on it says anyone on the internet can view such a report without authentication, including detail-level data, and that the option is unavailable for reports that rely on row-level security. With Direct Lake, Microsoft's overview recommends a fixed identity cloud connection when row-level security is used. An Import model is also a copy, so if your agreement asks you to delete data when it ends, the data stored in the semantic model goes with the tables.
How to read the data
The Market Series windows of 7, 30 and 180 days each describe a complete period, and a row is written once when its week closes. A posting records an intention to hire, so read a hiring chart as the roles a company publishes. A detection shows presence on the website, so read Technology Changes as the dated record of what each site runs.
Power Query's Json.Document can parse the JSON cells, which suits small tables, but expanding them once upstream suits large ones. Sector hiring trends for macro and thematic research and measuring an installed base and its weekly movement read the same series for two uses.
Frequently asked questions
How should hiring data be modelled in Power BI?
Use a star schema: a Company dimension, a Date dimension and one fact table for each Fokals dataset. Treat new and closed postings as flows that add over days, open postings as a level that needs the latest value for each company, and Market Series as a separate fact with no company key whose rows are never added together.
Why is the total of open postings too high in my report?
Open postings are a level at the end of each day, so a plain sum over a month counts the same posting once for every day it stayed open. Take each company's latest value in the period and sum those, as the Open postings latest measure does, or show the value for a single day.
How often can a Power BI semantic model be refreshed?
Microsoft's refresh page limits scheduled refresh to eight times a day on shared capacity and 48 on Premium, Premium per user or Fabric capacity. Fokals daily tables are written once a day and weekly tables once a week, so the limit is rarely the constraint. Schedule the refresh after your own load, not by the clock.
Can row-level security enforce a data licence?
It can enforce a scope you define, such as which users see which companies, because it filters rows by a DAX rule. It does not restrict workspace Admins, Members or Contributors, does not filter columns, and does not cover tables with no company key. Keep the editing roles few and settle the scope in the written agreement first.
How does Fokals data get into a Power BI model?
Fokals is delivered direct, by REST API and as bulk files in JSON, JSON Lines or CSV. You load the files or the API output into a lakehouse, warehouse or database that Power BI reads, or import the files with Power BI's own loaders, and build the semantic model on those tables. A first load comes from a bulk export, and the API keeps it current.
Can I publish a report on company signals to the web?
Not if the data is licensed to you. Microsoft's page says Publish to web lets anyone on the internet view the report without authentication, including the detail-level data behind it, and tells publishers not to publish confidential or proprietary information. Use the embed options that enforce permissions, and settle who may see the report in your agreement.
The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.
What this page says about the products it names was checked against their public documentation on 4 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.