Forum Discussion

MyPowerbi2020's avatar
MyPowerbi2020
Helper IV
5 years ago
Solved

Need Help With DAX

 

Dear Team,

I need to return a table based on the slicer selection. I did the following,

here Date is the slicer & my table name is Data. Not sure why this is not working , kindly help.

 

Table =
var __SelectedValue = SELECTEDVALUE(Data[Date])
return
Filter(
'Data',
'Data'[Date] = __SelectedValue
)
 
Thanks.
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi MyPowerbi2020 ,

    You can follow the steps in the below screen shot:

    1. Create a Date table and slicer with date field in Date table

    2. Create a measure as below to get the count of clicker and non clicker

    Count =
VAR _startdate =
    MIN ( 'Date'[Date] )
VAR _enddate =
    MAX ( 'Date'[Date] )
VAR _tab =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'EmailInfo' ),
            'EmailInfo'[Date] >= MIN ( 'Date'[Date] )
                && 'EmailInfo'[Date] <= MAX ( 'Date'[Date] )
        ),
        EmailInfo[Email],
        "nClicked", MAX ( 'EmailInfo'[Clicked] )
    )
RETURN
    COUNTX ( FILTER ( _tab, [nClicked] = MAX ( 'EmailInfo'[Clicked] ) ), [Email] )

    Best Regards

    Rena

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    If you are trying to return a calculated table, you can't have that respond to a slicer.  And you can't return a table in a measure (just some aggregation of one).  If you describe more about what you are trying to achieve, an alternate solution can be proposed.

     

    Regards,

    Pat

     

    • MyPowerbi2020's avatar
      MyPowerbi2020
      Helper IV
      5 years ago

      Kindly find below the requirement:

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

      https://drive.google.com/file/d/1iZGAcXAye5xlyshEgpg9IGiiHqEXQF0h/view?usp=sharing

      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.

      Final Output in the report should be,

      Status COUNT

      Clicker 2

      Non Clicker 1



      Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi MyPowerbi2020 ,

        You can follow the steps in the below screen shot:

        1. Create a Date table and slicer with date field in Date table

        2. Create a measure as below to get the count of clicker and non clicker

        Count =
VAR _startdate =
    MIN ( 'Date'[Date] )
VAR _enddate =
    MAX ( 'Date'[Date] )
VAR _tab =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'EmailInfo' ),
            'EmailInfo'[Date] >= MIN ( 'Date'[Date] )
                && 'EmailInfo'[Date] <= MAX ( 'Date'[Date] )
        ),
        EmailInfo[Email],
        "nClicked", MAX ( 'EmailInfo'[Clicked] )
    )
RETURN
    COUNTX ( FILTER ( _tab, [nClicked] = MAX ( 'EmailInfo'[Clicked] ) ), [Email] )

        Best Regards

        Rena

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    MyPowerbi2020 , Try a new table like

     

    calculatetable(Table,Filter('Data','Data'[Date] = __SelectedValue))

    or

    calculatetable(Table,Filter('Data','Data'[Date] in allselected(Data[Date) ))

    or

    calculatetable(Table,Filter('Data','Data'[Date] in values(Data[Date) ))