Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Flag based on the occurrences

I have a table(Name Table : accounts) which is mapped with many other tables in the direct model. In the accounts table , a column has duplicate values in it . I need create a measeaure to calculate the occurence of the values in that filed . If the occurenace of that values with the the column is greater than 1 than Y else N. 

I have already written using IF condition , which i m getting incorrect data while selecting any values in the filter .  

So, can you how to write a dax measure for it . 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    Hi Anonymous ,

     

    If you do not want to give the zero count flag N, you try to  change the measure to following

     

    Flag =
VAR c =
    COUNTROWS ( 'EVENT PARTICIPATION DETAIL' )+0
RETURN
    IF ( c = 0, BLANK (), IF ( c > 1, "Y", "N" ) )

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

20 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    We can create measure using following DAX to meet your requirement,

     

    Flag =
IF (
    COUNTROWS (
        FILTER ( ALL ( 'Table' ), 'Table'[value] = SELECTEDVALUE ( 'Table'[value] ) )
    ) > 1,
    "Y",
    "N"
)

     

    If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    BTW, pbix as attached.

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Sir,

      Very thanks for response .

       

      firstly, i m using direct query model . I cannot share u the file as i m in secure network. 

      However i tried to use the dax expression which you have given but i m getting error as shown below

       

      in the above screenshot , i have select one acctont from the filter where i have 7 con_1 values associated with that account . in the table i have consider the account, con_1, coun(con_1) and the flag dax expression where i m getting erroe. 

      Kindly help me .

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Acct_no is from Accounts table

      Con_1 is from EVENT PARTICIPATION DETAIL tables 

       

      i have re-written ur dax expression as 

      Flag =
      IF (
      COUNTROWS (
      FILTER ( ALL ( 'EVENT PARTICIPATION DETAIL' ), 'EVENT PARTICIPATION DETAIL'[Con_1] = SELECTEDVALUE ( 'EVENT PARTICIPATION DETAIL'[Con_1] ) )
      ) > 1,
      "Y",
      "N"
      )
       
      let me know if i m incorect 
      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        The DirectQuery Mode actually has a limitation of maximum rows, but for your visual there is only 7 rows in this account filter, could you test this measure for a card visual, filter only one Con_1 and ACCT_NUMBER to see if this DAX reach the limitation?

         

        Best regards,

        Community Support Team _ DongLi
        If this post helps, then please consider Accept it as the solution to help the other members find it more