How to create CG Measures , Date Dimension Table , Calculated Columns through Rest API .

[adeeln]

Hi Experts,
My Client wants to create Power BI Dataset through Rest API. Our Data Source Is Azure SQL Server.
I have found some documentation to Push the dataset from Source to Power BI Service Here are their Links.
1.Link 1 

2.Link 2 

But I did not find out any documentation or video for creating the following things through Rest API.

1 . Date Dimension Table

Example: Date Dimension Table Dax Code in Power BI given following.

DimDate = 

ADDCOLUMNS (

 CALENDAR (

 MIN ( FactAccountPayable[DueDate] ),

 MAX ( FactAccountPayable[DueDate] )

 ),

 "DateYear", FORMAT ( [Date], "YYYY" ),

 "MonthYear", FORMAT ( [Date], "MMM YYYY" ),

 "MonthYearNbr", FORMAT ( [Date], "YYYYMM" )

)

2 . DAX Table

Example: Table Dax Code in Power BI given the following.

Total Outstanding Amount =
SUMMARIZE (
FactAccountPayable,
FactAccountPayable[DocId],
FactAccountPayable[ClearingFlag],
"Total Outstanding Amount", SUM ( FactAccountPayable[OutstandingAmount] )
)

3 .Calculated Columns

Example: Calculated Column Example is given below.

1 . Company =RELATED( Company[Company] )

                                                                                         OR
2. Date Diff Due Since =DATEDIFF (

FactAccountPayable[DueDate],

TODAY(),

DAY

)

4 . Measures Table: I have created this table using the power bi default "Enter Data" function. Then just create measures into it.

5. Calculation Groups

Please help me and let me know how to do all the above things and also let me know if there any limitations for these.
Any help would be appreciated.

Thanks and Regards.

Adeel

[v-shex-msft]

Hi @adeeln,

Dax functions are based on the data model(AS instances), they weren't able to be used in the rest API.

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Docs
For the calendar table, you need to manually create a data table with date values and different types of Dimension fields.

Reference link about generate calendar table based on C# language:

Generate Dimension Table in C# · GitHub
Regards,
Xiaoxin Sheng

[adeeln]

@v-shex-msft Thanks for your reply. 
So it means we can't create a dynamic date dimensions table through Rest APIs.
Can we create calculated columns through Rest API? And what about the Calculation group.Can we create  Calculation groups through Rest API?

[v-shex-msft]

Hi @adeeln,

#1, Calculate fields are based on DAX formulas, it required AS data engine, so you can't use the rest API to create it.

#2, AFAIK, calculate group are host on the date model, it also not able to be changed by rest API.

Creating Calculation Groups in Power BI Desktop - SQLBI

Regards,

Xiaoxin Sheng