JSON to PowerBI using Data Factory, what storage to use

Assuming you already have the data in Azure SQL:

Use Power BI desktop to connect to the data in Azure SQL ("Get Data").

Import mode is generally the recommended option for Power BI storage mode. Usually, DirectQuery is not recommended.

Perform transformations in Power Query as required ("Transform data").

Do the data modelling in Power BI Desktop.

Apply Row Level Security (RLS) as required. (Use Tabular Editor if you need OLS).

Create the visuals and report pages.

From Power BI desktop, publish your report to a workspace in Powerbi.com (aka Power BI service).

In Powerbi.com, select the workspace and in there find the semantic model you have published. Then enable scheduled refresh for the semantic model. That way, the data in the report will get automatically refreshed according to a schedule.

If you have Fabric license, then you can consider using Fabric Lakehouse instead of Azure SQL database.

"so it means I need to update manually the dashboard daily on PBI desktop in order for the PBI.com user to view the up to date data, or I'm missing something" You don't need to refresh the data in Power BI Desktop. You can enable scheduled refresh in Powerbi.com (Power BI service). See e.g. here: https://learn.microsoft.com/en-us/training/modules/manage-datasets-power-bi/5-dataset-refresh