Forum Discussion
Create calculated column based on single value in another column
Hi Anonymous & all,
Problem: I want to create a column CLICKED(Y/N) on the basis of clicks column where if the value of clicks =1 for any id then the value then the value for clicked column will be Y else N .
Below is the sample data .
| ID | DATE | CLICKS | CLICKED (Y/N) |
| 1 | 1 may 2021 | 0 | Y |
| 1 | 3 may 2021 | 0 | Y |
| 1 | 7 may 2021 | 1 | Y |
| 2 | 1 may 2021 | 0 | N |
| 2 | 2 may 2021 | 0 | N |
| 3 | 1 may 2021 | 0 | Y |
| 3 | 5 may 2021 | 1 | Y |
| 3 | 6 may 2021 | 0 | Y |
Thanks in advance
Sumesh
SUMESHKUMAR22 Try:
Clicked (Y/N) = VAR __ID = [ID] VAR __Max = MAXX(FILTER(ALL('Table'),[ID]=__ID),[CLICKS]) RETURN IF(__Max = 1,"Y","N")Hi, SUMESHKUMAR22
- Calculated column
Clicked (Y/N) = IF ( CALCULATE ( MAX ( 'Table'[CLICKS] ), FILTER ( 'Table', [ID ] = EARLIER ( 'Table'[ID ] ) ) ) = 1, "Y", "N" )2. Measure
Clicked (Y/N) M = IF ( CALCULATE ( MAX ( 'Table'[CLICKS] ), FILTER ( ALL('Table'), [ID ]= MAX( 'Table'[ID ] ) ) ) = 1, "Y", "N" )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
SUMESHKUMAR22 Try:
Clicked (Y/N) = VAR __ID = [ID] VAR __Max = MAXX(FILTER(ALL('Table'),[ID]=__ID),[CLICKS]) RETURN IF(__Max = 1,"Y","N")Hi Greg_Deckler ,
Thanks for the workaround!
If I want to create a Measure then what it would be because I want the Y & N should return dynamically.
Thanks! again for the prompt reply
Hi, SUMESHKUMAR22
- Calculated column
Clicked (Y/N) = IF ( CALCULATE ( MAX ( 'Table'[CLICKS] ), FILTER ( 'Table', [ID ] = EARLIER ( 'Table'[ID ] ) ) ) = 1, "Y", "N" )2. Measure
Clicked (Y/N) M = IF ( CALCULATE ( MAX ( 'Table'[CLICKS] ), FILTER ( ALL('Table'), [ID ]= MAX( 'Table'[ID ] ) ) ) = 1, "Y", "N" )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Greg_Deckler and Anonymous ,
Can you help me out with the Measure formula for the above calculated Column. because I have a filter for category name & data range in the report page & the same ID can be there for other cateogry as well so to filter based on category & dates to get the desired result.
Thanks in advance
