Forum Discussion
Help with query DAX for adding a new column with a certain value based on simple logic
Hi Community,
I would like your help on a query I have and qhat DAX query I need to use to establish the following:
I currently (table 1) have this data. And I would like to have in the Payed Column a logic applied with would give value "Y" in case the ID also has a A2 Event or a 0 amount (same). And otherwise "N". Resulting in table 2.
I'm stuck, could someone help? Much appreciated!
| ID | Event | Amount | Payed |
| 1 | A1 | 10 | |
| 2 | A1 | 20 | |
| 3 | A1 | 30 | |
| 4 | A1 | 12 | |
| 5 | A1 | 368 | |
| 6 | A1 | 12 | |
| 7 | A1 | 14 | |
| 1 | A2 | 0 | |
| 3 | A2 | 0 | |
| 4 | A2 | 0 | |
| 5 | A2 | 0 |
Table 2
| ID | Event | Amount | Payed |
| 1 | A1 | 10 | Y |
| 2 | A1 | 20 | N |
| 3 | A1 | 30 | Y |
| 4 | A1 | 12 | Y |
| 5 | A1 | 368 | Y |
| 6 | A1 | 12 | N |
| 7 | A1 | 14 | N |
| 1 | A2 | 0 | Y |
| 3 | A2 | 0 | Y |
| 4 | A2 | 0 | Y |
| 5 | A2 | 0 | Y |
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Payed CC = IF ( COUNTROWS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) && CONTAINS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) ), Data[Event], "A2" ) && CONTAINS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) ), Data[Amount], 0 ) ) ) > 0, "Y", "N" )Hi,
thank you for your message.
could you please try the below? it is for creating a calculated column.
Paid CC = VAR _IDcontainsA2 = SUMMARIZE ( FILTER ( Data, Data[Event] = "A2" ), Data[ID] ) RETURN IF ( Data[ID] IN _IDcontainsA2, "Y", "N" )
3 Replies
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Payed CC = IF ( COUNTROWS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) && CONTAINS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) ), Data[Event], "A2" ) && CONTAINS ( FILTER ( Data, Data[ID] = EARLIER ( Data[ID] ) ), Data[Amount], 0 ) ) ) > 0, "Y", "N" )Hi Johwan_Kim,
Thanks again for your help on my previous query. The data changed a bit, and I was wondering if you could help again, sorry. BUt very much appreciated!
I still want to end up with the same result. Thus, I would like to have in the Payed Column a logic applied with would give value "Y" in case the ID also has a A2 Event. And otherwise "N". Resulting in table 2.
The yable is now as follow:ID Event Date Amount 1 A1 1-7-2022 10 2 A1 1-7-2022 20 3 A1 1-7-2022 30 5 A1 1-7-2022 368 5 A2 1-7-2022 0 7 A1 1-7-2022 14 1 A4 2-7-2022 NULL 4 A1 2-7-2022 12 6 A1 3-7-2022 12 3 A3 4-7-2022 45 6 A3 4-7-2022 18 1 A5 5-7-2022 NULL 4 A2 9-7-2022 0 1 A2 10-7-2022 0 3 A2 11-7-2022 0 And I would like it to be:
ID Event Date Amount Payed CC 1 A1 1-7-2022 10 Y 2 A1 1-7-2022 20 N 3 A1 1-7-2022 30 Y 5 A1 1-7-2022 368 Y 5 A2 1-7-2022 0 Y 7 A1 1-7-2022 14 N 1 A4 2-7-2022 NULL Y 4 A1 2-7-2022 12 Y 6 A1 3-7-2022 12 N 3 A3 4-7-2022 45 Y 6 A3 4-7-2022 18 N 1 A5 5-7-2022 NULL Y 4 A2 9-7-2022 0 Y 1 A2 10-7-2022 0 Y 3 A2 11-7-2022 0 Y Hi,
thank you for your message.
could you please try the below? it is for creating a calculated column.
Paid CC = VAR _IDcontainsA2 = SUMMARIZE ( FILTER ( Data, Data[Event] = "A2" ), Data[ID] ) RETURN IF ( Data[ID] IN _IDcontainsA2, "Y", "N" )