Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi!
I'm developing a DAX formula to show the projects that obey the rule below:
IF (ACTIVITY = A2 AND STATUS= ENCERRADO) AND (ACTIVITY = A3 AND STATUS= PENDENTE):
COLUMN FORMULA = "TRUE"
ELSE:
BLANK
PROJECT | ACTIVITY | STATUS |
P1 | A1 | ENCERRADO |
P1 | A2 | PENDENTE |
P1 | A3 | PENDENTE |
P1 | A4 | PENDENTE |
P1 | A5 | PENDENTE |
P2 | A1 | ENCERRADO |
P2 | A2 | ENCERRADO |
P2 | A3 | PENDENTE |
P2 | A4 | PENDENTE |
P2 | A5 | PENDENTE |
P3 | A1 | ENCERRADO |
P3 | A2 | ENCERRADO |
P3 | A3 | ENCERRADO |
P3 | A4 | PENDENTE |
P3 | A5 | PENDENTE |
P4 | A1 | ENCERRADO |
P4 | A2 | ENCERRADO |
P4 | A3 | PENDENTE |
P4 | A4 | PENDENTE |
P4 | A5 | PENDENTE |
CORRECT ANSWER:
P1 | A1 | ENCERRADO | JUN | |
P1 | A2 | PENDENTE | A2PENDENTE | |
P1 | A3 | PENDENTE | A3PENDENTE | |
P1 | A4 | PENDENTE | A4PENDENTE | |
P1 | A5 | PENDENTE | A5PENDENTE | |
P2 | A1 | ENCERRADO | A1ENCERRADO | |
P2 | A2 | ENCERRADO | A2ENCERRADO | TRUE |
P2 | A3 | PENDENTE | A3PENDENTE | TRUE |
P2 | A4 | PENDENTE | A4PENDENTE | |
P2 | A5 | PENDENTE | A5PENDENTE | |
P3 | A1 | ENCERRADO | A1ENCERRADO | |
P3 | A2 | ENCERRADO | A2ENCERRADO | |
P3 | A3 | ENCERRADO | A3ENCERRADO | |
P3 | A4 | PENDENTE | A4PENDENTE | |
P3 | A5 | PENDENTE | A5PENDENTE | |
P4 | A1 | ENCERRADO | A1ENCERRADO | |
P4 | A2 | ENCERRADO | A2ENCERRADO | TRUE |
P4 | A3 | PENDENTE | A3PENDENTE | TRUE |
P4 | A4 | PENDENTE | A4PENDENTE | |
P4 | A5 | PENDENTE | A5PENDENTE |
How can my dax formula pick up the valeu below the first if statement to show the correct project?
I have made this formula concatenating the ACTIVITY and STATUS columns, but it doesnt show all the projects because its missing a IF statement
COND = IF(teste[JUN] = "A2ENCERRADO";"TRUE";BLANK())
Solved! Go to Solution.
I found a solution
First I made a calculated column
Column = IF ( OR ( AND ( teste[ACTIVITY] = "A2"; teste[ESTATUS] = "ENCERRADO" ); AND ( teste[ACTIVITY] = "A3"; teste[ESTATUS] = "PENDENTE" ) ); TRUE (); FALSE() )
And than a measure with a slicer to get only true subjects in a sequence
Column 3 = COUNTROWS(filter(teste;teste[Column]=TRUE()))
You can also use the switch function:
Column = SWITCH ( TRUE (); 'Table'[Activity] = 'A2' && 'Table'[Status] = 'ENCERRADO'; 'TRUE'; 'Table'[Activity] = 'A3' && 'Table'[Status] = 'PENDENTE'; 'TRUE'; BLANK() )
Hi, you can try && instead of AND in your formula or can try "AND" dax function:
= IF ( AND ( AND ( ACTIVITY = A2, STATUS = ENCERRADO ), AND ( ACTIVITY = A3, STATUS = PENDENTE ) ), TRUE (), BLANK () )
Let me know if it works
Happy to help!
Hello, thank you for your support, but with this formula every result in the custom column was "False"
CODIGO = IF (
AND (
AND ( teste[ACTIVITY] = "A2"; teste[STATUS] = "ENCERRADO" );
AND ( teste[ACTIVITY] = "A3"; teste[STATUS] = "PENDENTE" )
);
TRUE ();
BLANK ()
)
Are you sure you have to ask with AND? this means all the columns should respond to that condition in order to show true(). Maybe you need an "OR" in some place.
I think you are looking for this:
= IF ( OR ( AND ( ACTIVITY = A2, STATUS = ENCERRADO ), AND ( ACTIVITY = A3, STATUS = PENDENTE ) ), TRUE (), BLANK () )
Happy to help!
I tried to use the conditionals but they did not work.
Only if A2 and A3 are in the sequence reported in the correct answer that the formula must be correct as well.
IF A2 = ENCERRADO and next line A3 = PENDENTE:
"TRUE"
ELSE:
"FALSE"
I found a solution
First I made a calculated column
Column = IF ( OR ( AND ( teste[ACTIVITY] = "A2"; teste[ESTATUS] = "ENCERRADO" ); AND ( teste[ACTIVITY] = "A3"; teste[ESTATUS] = "PENDENTE" ) ); TRUE (); FALSE() )
And than a measure with a slicer to get only true subjects in a sequence
Column 3 = COUNTROWS(filter(teste;teste[Column]=TRUE()))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
83 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |