DAX Command Difference to Calculate Set of Data

[Irwan]

Hello Power BI Expert,

I am nearly completing my report in Power BI. However, it comes to my mind that I used two form of DAX when using "New Measure". Since I self-taught in Power BI, so here i am trying to understand what is the difference between these form of DAX writing (I use these two form the most): 

1. Calculate(Countrows(table),Filter1,Filter2,Filter3))

2. Countrows(Filter(table),Filter1&&Filter2&&Filter3)

Both seem to provide same result in my data when calculating number of data in table.

Another thing is, when i asked other people how to calculate data, they provide me with something like below. Looks like from SQL form of writing since I see that "window func SQL" (I might be wrong).

the writing seems to be different than DAX. Is Power BI having more languange than DAX to be used in calculation?

Thank you in advance.

[123abc · Accepted solution]

In Power BI, the primary language for creating calculations and measures is Data Analysis Expressions (DAX). However, there are other languages and functions you can use within Power BI to manipulate and transform data. Let's address your questions:

**1. DAX Expressions:**
- The two DAX expressions you provided are both valid and can be used to achieve similar results. They are used for filtering and counting rows in a table based on specified conditions.
- The first expression uses the `CALCULATE` function to modify the filter context and then counts the rows that meet the conditions specified in `Filter1`, `Filter2`, and `Filter3`.
- The second expression uses the `FILTER` function to create a filtered table based on the conditions `Filter1 && Filter2 && Filter3` and then counts the rows in that filtered table.
- Both approaches are acceptable, and the choice between them often depends on your preference and the complexity of your DAX measures.

**2. SQL-Like Syntax:**
- The snippet you provided that resembles SQL syntax might be part of a more complex DAX expression or might be referring to a different language altogether. DAX itself is different from SQL, although they share some similarities, especially when it comes to filtering and aggregating data.
- If you encounter DAX expressions that look like SQL, they are likely DAX functions designed to perform similar operations to SQL queries, but the syntax can be different.

Power BI primarily relies on DAX for calculations and measures. However, it also supports other languages and techniques for data transformation and modeling:

- **Power Query:** Power Query, also known as the Power Query Editor, is a separate tool within Power BI that uses a language called M for data transformation. M is used to load, clean, and shape data before it is imported into the data model. While it's not used for calculations within the model, it's essential for data preparation.

- **SQL Queries:** Power BI can also connect to SQL databases, and you can use SQL queries to extract and transform data before loading it into Power BI. SQL queries are typically used in the "Advanced Editor" of Power Query to perform complex data transformations.

- **R and Python:** Power BI supports integrating R and Python scripts for advanced analytics and custom visualizations. You can write R and Python code directly in Power BI to extend its functionality.

In summary, while DAX is the primary language for creating measures and calculations within Power BI, other languages like M, SQL, R, and Python can be used for specific tasks such as data transformation, data import, advanced analytics, and custom visualizations. The choice of language depends on the specific requirements of your Power BI project.

[Irwan]

Thank you and really appreaciate for those explanations, it gives me some insight on Power BI capabilities.