DAX Requirement || HELP

[MyPowerbi2020]

Hi All,

Need your help.

The input data is as follows: (Please note the input data tab)

https://drive.google.com/file/d/1xsrhMOGCSZNUAuCrMhw9Vj2DUeOja_QY/view

Requirement:

basically based on the date selection if the max value for the Clicked column is 1 aganst the email then the user should be treated as Clicker.

Output example (as stated in the output data tab):

Here for this output data the logic is - the user has selected 2 dates: August 20 and August 21, since for the ABC email the maximum value of the column that was clicked (in the data input) is 1 in the given data range, the ABC email should be treated as Clicker.

The final output needs to be like the Final Output section , Output Data tab.

Thank you.

[v-yingjl · Accepted solution]

Hi @MyPowerbi2020 ,

1. Create a [Clicker?] measure:

Clicker? = 
IF ( MAX ( 'InputData'[Clicked] ) = 1, "Clicker", "Non Clicker" )

2. Create a new table which includes [Status] field:

 

3. Create a [Count] measure:

Count =
CALCULATE (
    DISTINCTCOUNT ( InputData[Email] ),
    FILTER (
        ALL ( 'InputData'[Email] ),
        [Clicker?] IN DISTINCT ( 'Table'[Status] )
    )
)

You will get your expected result:

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@MyPowerbi2020 


You will find the answer in this thread.

https://community.powerbi.com/t5/Translated-Spanish-Desktop/Necesita-ayuda-con-DAX/m-p/1317152#M9736...

Count Email = 
var c = SELECTEDVALUE('Clicker Table'[Clicker]) return

SUMX(
    VALUES('Table'[Email]),
    IF( [Click Status] = c , 1 , 0)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[mahoneypat]

@MyPowerbi2020  You can make a table visual with your email column and this expression to get your desired result

Clicked? = IF(MAX(Clickers[Clicked])=1, "Clicker", "Not Clicker")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[MyPowerbi2020]

Hi,

No my final out is different , kindly check the doc.

Thanks.

[mahoneypat]

I looked again @MyPowerbi2020  .  Here is how you can get that output.

You need a table that has your values of Clicker and Not Clicker.  I did it with DATATABLE as a calculated table but you could add through query

Status = DATATABLE("Status",STRING,{{"Clicker"}, {"Not Clicker"}})

Create the same measure I proposed earlier

Clicked? = IF(MAX(Clickers[Clicked])=1, "Clicker", "Not Clicker")

And make this new measure

Click Count = var thisstatus = SELECTEDVALUE('Status'[Status])

return COUNTROWS(FILTER(VALUES(Clickers[Email]), [Clicked?] = thisstatus))

Use the Click Count measure in a table with your new Status[Status] column to get your desired result.  Note there is not relationship between the tables.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat