Forum Discussion

MyPowerbi2020's avatar
MyPowerbi2020
Icon for Helper IV rankHelper IV
6 years ago
Solved

DAX Requirement || HELP

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's avatar
    v-yingjl
    6 years ago

    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.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      MyPowerbi2020
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi,

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

      Thanks.

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    MyPowerbi2020 


    You will find the answer in this thread.

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

    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

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    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.