Create calculated column based on single value in another column

[SUMESHKUMAR22]

Hi @v-eqin-msft  & 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

[Greg_Deckler · Accepted solution]

@SUMESHKUMAR22 Try:

Clicked (Y/N) = 
  VAR __ID = [ID]
  VAR __Max = MAXX(FILTER(ALL('Table'),[ID]=__ID),[CLICKS])
RETURN
  IF(__Max = 1,"Y","N")

[v-zhangti · Accepted solution]

Hi, @SUMESHKUMAR22 

1. 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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-zhangti · Accepted solution]

Hi, @SUMESHKUMAR22 

1. 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.

[SUMESHKUMAR22]

Hi @Greg_Deckler and @v-eqin-msft ,

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

[Greg_Deckler · Accepted solution]

@SUMESHKUMAR22 Try:

Clicked (Y/N) = 
  VAR __ID = [ID]
  VAR __Max = MAXX(FILTER(ALL('Table'),[ID]=__ID),[CLICKS])
RETURN
  IF(__Max = 1,"Y","N")

[praveenpasila]

Thank you Greg helped me solve my problem

[SUMESHKUMAR22]

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