Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to build a measure using IF or SWITCH, but DAX is not allowing me to load the tables:
Thanks a lot
Daniel
Solved! Go to Solution.
The number 1 means first element?
May I use this too?
IF( CONTAINS ( 'Fluxo de caixa', 'Fluxo de caixa'[Tipo], "Entrada" ); SUM('Fluxo de caixa'[Valor]) + SUM('Fluxo de caixa'[Saldo final de caixa]); SUM('Fluxo de caixa'[Valor]) - SUM('Fluxo de caixa'[Saldo final de caixa]) )
The second argument of FIRSTNONBLANK is rarely used. 1 returns a value of "True" and allows the function to work without an expression there. I think the second argument of FIRSTNONBLANK ought to be optional but I didn't write the language.
CONTAINS won't work the way you're expecting. Does the way I wrote it not give you the results you're looking for?
Try this instead:
CALCULATE( SUM('Fluxo de caixa'[Valor]) + SUM('Fluxo de caixa'[Saldo final de caixa]); FILTER( 'Fluxo de caixa'; 'Fluxo de caixa'[Tipo] = "Entrada" ) ) + CALCULATE( SUM('Fluxo de caixa'[Valor]) - SUM('Fluxo de caixa'[Saldo final de caixa]); FILTER( 'Fluxo de caixa'; 'Fluxo de caixa'[Tipo] <> "Entrada" ) )
Proud to be a Super User!
You can't refer to naked tables or columns in measures, you have to aggregate the data (sum, min, max, firstnonblank, etc.).
Proud to be a Super User!
I see.
But I would like to work with string:
Saldo final = IF('Fluxo de caixa'[Tipo]="Entrada";SUM('Fluxo de caixa'[Valor])+SUM('Fluxo de caixa'[Saldo final de caixa]);sum('Fluxo de caixa'[Valor])-sum('Fluxo de caixa'[Saldo final de caixa]))
Fluxo de caixa[Tipo] only has text.
How can I aggregate the data?
Proud to be a Super User!
Saldo final = IF(FIRSTNONBLANK('Fluxo de caixa'[Tipo];'Fluxo de caixa'[Tipo]="Entrada");SUM('Fluxo de caixa'[Valor])+SUM('Fluxo de caixa'[Saldo final de caixa]);sum('Fluxo de caixa'[Valor])-sum('Fluxo de caixa'[Saldo final de caixa]))
Is not working, IF column Tipo contains "Entrada", sum value in valor and value in Saldo final de caixa...
Any clue?
Your syntax for FIRSTNONBLANK is slightly wrong.
IF( FIRSTNONBLANK('Fluxo de caixa'[Tipo]; 1) = "Entrada"; SUM('Fluxo de caixa'[Valor]) + SUM('Fluxo de caixa'[Saldo final de caixa]); SUM('Fluxo de caixa'[Valor]) - SUM('Fluxo de caixa'[Saldo final de caixa]) )
Proud to be a Super User!
The number 1 means first element?
May I use this too?
IF( CONTAINS ( 'Fluxo de caixa', 'Fluxo de caixa'[Tipo], "Entrada" ); SUM('Fluxo de caixa'[Valor]) + SUM('Fluxo de caixa'[Saldo final de caixa]); SUM('Fluxo de caixa'[Valor]) - SUM('Fluxo de caixa'[Saldo final de caixa]) )
The second argument of FIRSTNONBLANK is rarely used. 1 returns a value of "True" and allows the function to work without an expression there. I think the second argument of FIRSTNONBLANK ought to be optional but I didn't write the language.
CONTAINS won't work the way you're expecting. Does the way I wrote it not give you the results you're looking for?
Try this instead:
CALCULATE( SUM('Fluxo de caixa'[Valor]) + SUM('Fluxo de caixa'[Saldo final de caixa]); FILTER( 'Fluxo de caixa'; 'Fluxo de caixa'[Tipo] = "Entrada" ) ) + CALCULATE( SUM('Fluxo de caixa'[Valor]) - SUM('Fluxo de caixa'[Saldo final de caixa]); FILTER( 'Fluxo de caixa'; 'Fluxo de caixa'[Tipo] <> "Entrada" ) )
Proud to be a Super User!
Yes, worked very well! Now I know how to use the IF statement.
Nice line of thought, I gonna use this last suggestion!
Thanks a lot!
Daniel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |