March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I'm currently working on upgrading my measure and have hit a bit of a roadblock. Here’s where I’m at:
Current Setup
I have a working solution, but I need to add some additional conditions.
Analitico_Valor_Realiz =
VAR MinContaIni = MIN('Visao'[Conta_Ini])
VAR MaxContaFinal = MAX('Visao'[Conta_Final])
VAR MinCCustoIni = MIN('Visao'[CCusto_Ini])
VAR MaxCCustoFinal = MAX('Visao'[CCusto_Final])
RETURN
CALCULATE(
SUM('Dados'[VALOR_REALIZADO]),
'Dados'[CODIGO_CONTA] >= MinContaIni &&
'Dados'[CODIGO_CONTA] <= MaxContaFinal &&
'Dados'[CENTRO_CUSTO] >= MinCCustoIni &&
'Dados'[CENTRO_CUSTO] <= MaxCCustoFinal
)+0
What I Need:
I need to incorporate additional conditions into the measure. I’m attaching a PBIX file that currently works, as well as an Excel file that contains both the base data and the expected result, which I achieve through formulas.
= SUMIF ( 'Visao'[Entid_Super];
'Visao'[Entid_Gerenc];
'Dados'[VALOR(REALIZADO] )
Context:
The 'Visao' dimension table comes from an ERP system (Totvs Protheus). I’m attempting to create a DRE (Income Statement). Both the 'Visao' dimension table and the necessary data are fed into the PBIX file through SQL.
However, I also need to include additional conditions. I'm attaching a PBIX file which currently works, and a Excel file which contains both the base data and the expected result, which I achieve thorugh formulas.
Current result:
What i want to achieve:
Could someone assist me with this, or provide some guidance on possible solutions?
Thank you very much!
Solved! Go to Solution.
Instead of creating a measure, I created a relationship between the tables.
While power bi doesn't accept range relationships:
('Dados'[Contas] Between
'Visao'[Conta_Ini] and
'Visao'[Conta_Final]
I expanded the possible accounts of 'Visao'[Conta_Final] and 'Visao'[Conta_Ini] using List.Text.
I also duplicated the 'Visao' and filted 'Analitica' and 'Sintetica', and created a relationship between them too.
I'm sorry; in hindsight, I wasn't clear about the image formatting.
The problem lies in the values.
I basically want to create an accounting report of the company's results (monthly).
Currently, the accounts (which are in the dimension table 'Visao') sum only the accounting recordings throughout the month, and these are "analitica" accounts.
However, there are grouped accounts that sum the "analytical" accounts. These are "sintetica" accounts.
So, while I have already solved summing the accounting records using the DAX measure, I now need to also group the sums.
I apologize for not creating the PBIX file and Excel file with the same formatting.
In the example (internet snippet, no correlation with my files) below, the green check marks are what I already have solved, and the orange x's are what I'm trying to achieve.
Instead of creating a measure, I created a relationship between the tables.
While power bi doesn't accept range relationships:
('Dados'[Contas] Between
'Visao'[Conta_Ini] and
'Visao'[Conta_Final]
I expanded the possible accounts of 'Visao'[Conta_Final] and 'Visao'[Conta_Ini] using List.Text.
I also duplicated the 'Visao' and filted 'Analitica' and 'Sintetica', and created a relationship between them too.
@jnsrkd - You don't need DAX for this.
You can use a Matrix Visual and use the column that defines "ANALITICA" and "SINTETICA" alongside the "Desc_Ent_Ger2" column in the rows section. You then just need to play around with the visual formatting to acheiv the result you want.
In the screenshot below, I have expanded both categories and set the layout style to "Tabular"
If this works, please accept as the solution for others visibility.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
19 | |
17 | |
9 | |
5 |
User | Count |
---|---|
36 | |
29 | |
16 | |
15 | |
12 |