Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SUMESHKUMAR22
Helper III
Helper III

Create calculated column based on single value in another column

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 DATECLICKSCLICKED (Y/N)
11 may 20210Y
13 may 20210Y
17 may 20211Y
21 may 20210N
22 may 20210N
31 may 20210Y
35 may 20211Y
36 may 20210Y


Thanks in advance
Sumesh

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@SUMESHKUMAR22 Try:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

v-zhangti
Community Support
Community Support

Hi, @SUMESHKUMAR22 

 

  1. Calculated column

 

Clicked (Y/N) =
IF (
    CALCULATE (
        MAX ( 'Table'[CLICKS] ),
        FILTER ( 'Table', [ID ] = EARLIER ( 'Table'[ID ] ) )
    ) = 1,
    "Y",
    "N"
)

 

vzhangti_0-1636940285570.png

 

     2. Measure

 

Clicked (Y/N) M = 
IF (
    CALCULATE (
        MAX ( 'Table'[CLICKS] ),
        FILTER ( ALL('Table'), [ID ]= MAX( 'Table'[ID ] ) )
    ) = 1,
    "Y",
    "N"
)

 

vzhangti_1-1636940344170.jpeg

 

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.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-zhangti
Community Support
Community Support

Hi, @SUMESHKUMAR22 

 

  1. Calculated column

 

Clicked (Y/N) =
IF (
    CALCULATE (
        MAX ( 'Table'[CLICKS] ),
        FILTER ( 'Table', [ID ] = EARLIER ( 'Table'[ID ] ) )
    ) = 1,
    "Y",
    "N"
)

 

vzhangti_0-1636940285570.png

 

     2. Measure

 

Clicked (Y/N) M = 
IF (
    CALCULATE (
        MAX ( 'Table'[CLICKS] ),
        FILTER ( ALL('Table'), [ID ]= MAX( 'Table'[ID ] ) )
    ) = 1,
    "Y",
    "N"
)

 

vzhangti_1-1636940344170.jpeg

 

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
Helper III
Helper III

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
Super User
Super User

@SUMESHKUMAR22 Try:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg helped me solve my problem

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


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.