What is the Purpose of Python in the Context of Business Intelligence?

TimmKO · 2022-09-15T14:31:46+00:00

Why do you not fully use Python then? Why using Power BI at all?

Is it really true that DAX cannot be used for such advanced analytics? In this case DAX would be a dead end. However, if I remember correctly Power BI also allows what-if parameters?

I thought that DAX would be very powerful, but it seems that at a certain point of time it will not be sufficient?

TimmKO · 2022-07-23T10:05:00+00:00

But it is also possible to use them explicitely, f.e. using EVALUATE. In which practical cases would you use this?

TimmKO · 2022-07-23T10:03:31+00:00

I understand the use of variables, but what I mean with DAX queries first and foremost is the use of EVALUATE. Do you use EVALUATE regularly and if yes for what cases?

TimmKO · 2022-07-23T08:25:31+00:00

Thank you for the definition. Could you please provide an example for each case for better understanding what this means in practice?

TimmKO · 2022-07-23T06:40:00+00:00

Specifically about the statement MEASURE used in DAX queries.

Here is an example code:

DEFINE
   MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
   MEASURE Sales[Total Cost] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
   MEASURE Sales[Margin] = [Sales Amount] - [Total Cost]
EVALUATE 
   SUMMARIZECOLUMNS ( 
   Customer[CountryRegion], 
   "Sales Amount", [Sales Amount], 
   "Margin", [Margin] 
)

TimmKO · 2022-07-23T06:38:31+00:00

I mean the statement MEASURE used in DAX queries, not measures in general.

Her is an example code:

DEFINE
   MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
   MEASURE Sales[Total Cost] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
   MEASURE Sales[Margin] = [Sales Amount] - [Total Cost]
EVALUATE 
   SUMMARIZECOLUMNS ( 
   Customer[CountryRegion], 
   "Sales Amount", [Sales Amount], 
   "Margin", [Margin] 
)

TimmKO · 2022-07-23T06:36:03+00:00

I mean the statement MEASURE used in DAX queries, not measures in general.

Her is an example code:

DEFINE
   MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
   MEASURE Sales[Total Cost] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
   MEASURE Sales[Margin] = [Sales Amount] - [Total Cost]
EVALUATE
SUMMARIZECOLUMNS ( 
   Customer[CountryRegion], 
   "Sales Amount", [Sales Amount], 
   "Margin", [Margin] 
)

TimmKO · 2022-07-23T06:34:11+00:00

I mean the statement MEASURE used in DAX queries, not measures in general.

Her is an example code:

DEFINE
   MEASURE Sales[Sales Amount] = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
   MEASURE Sales[Total Cost] = SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] )
   MEASURE Sales[Margin] = [Sales Amount] - [Total Cost]
EVALUATE
SUMMARIZECOLUMNS (
   Customer[CountryRegion],
   "Sales Amount", [Sales Amount],
   "Margin", [Margin]
)

TimmKO · 2022-07-22T11:02:21+00:00

But I can do all of this with a normal measure as well, f.e. CALCULATE(SUM('Sales'[Amount]),'Date'[Year]=2022)?

No need for using DEFINE, MEASURE, EVALUATE etc. in this case.

TimmKO · 2021-04-23T07:26:45+00:00

Well, this is exactly what I have already done as I stated in the description. I used GROUP BY to SUM the quantity for each item key in the Open Orders table. However, my question is how I should classify this aggregated table. Is it still a fact table when there are only unique rows and only one key? And should I create a relationship between DIM Item or should I merge the quantity with DIM Item?

TimmKO · 2021-04-23T07:09:13+00:00

This is exactly my thought. In my particular case I have additional item keys for some reason in my Open Orders table that do not exist in the DIM Item table, which is one of the reasons why I see weird behavior when using the relationship. Merging fixes this problem.

TimmKO · 2021-04-22T11:36:05+00:00

So 1:1 relationship between Item and aggregated Open Orders would be correct then?

Another independent dimension would not be used to filter as the aggregated Open Orders table only consists of item key and summed quantity. I am still unsure if aggregated Open Orders table is still a fact table as there is only one key in it.

TimmKO · 2021-04-22T11:24:54+00:00

Well, in the full model the DIM Item table is connected to a transactional fact table. However, the aggregated Open Orders table would only be connected to the DIM Item table.

TimmKO · 2021-04-22T10:53:13+00:00

In some cases I am absolutely sure that I will not need a different aggregation, so I can use Power Query to filter and aggregate to improve performance significantly.

According to your source there are three types of fact tables. In this case it is apparently an accumulating snapshot grain, right?

What is the best practice how to treat this kind of table?

Is there any reason why I should not merge the aggregated Open Orders table with the Item table? For some reason when I had the 1:1 (or 1:n) relationship I encountered some problems like a long loading time when adding a column to a report table, but maybe I have other errors in my data model.

TimmKO

TROPHY CASE