Flag based on the occurrences

[Anonymous]

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 . 

[v-lid-msft]

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]

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]

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

[Anonymous]

when i have added the same measure in the card visualization but still i m facing same error .. 

[v-lid-msft]

Hi @Anonymous ,

Can you try the following measure to see the result?

Flag =
VAR c1 =
    SELECTEDVALUE ( 'EVENT PARTICIPATION DETAIL'[Con_1] )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( 'EVENT PARTICIPATION DETAIL' ),
                'EVENT PARTICIPATION DETAIL'[Con_1] = c1
            )
        ) > 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.

[Anonymous]

Thanks for your resposne..

facing same error stated above

[v-lid-msft]

Hi @Anonymous ,

Does the account table has relation with the con table? Can you try to  put a filter on a account , then try to create this measure and use a card visual to show it.

Test = COUNTROWS( 'EVENT PARTICIPATION DETAIL'  )

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.

[Anonymous]

Does the account table has relation with the con table? YES

[v-lid-msft]

Hi @Anonymous ,

Based on my test, using variable or other function in the Filter function will reach the limitation of Directquery.

So try to use the following workaround what should works when table visual has only con_1 and Flag.

Flag = IF(COUNTROWS( 'EVENT PARTICIPATION DETAIL')>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.

[Anonymous]

i have remodified the given expression for inactive relationship. Can you tell me if this is correct 

Flag = IF(CALCULATE(COUNTROWS( 'EVENT PARTICIPATION DETAIL'),USERELATIONSHIP(ACCOUNTS[ACCOUNT_GREF],'EVENT PARTICIPATION DETAIL'[ACCOUNT_OWNER]))>1,"Y","N")

[Anonymous]

the relation between account and event table is inactive relationship. Consider this relationship to build the dax for counting the event_gref . 

[Anonymous]

Hi,

I have selected one acct number where the count of event_gref is 62 . But after applying the flag logic i see all the event_gref which are not related to account number . Could you kinldy help me.. 

[v-lid-msft · Accepted solution]

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.

[Anonymous]

[Anonymous]

*** Need Help***

i have written a dax query which returns values for the dimension wise .. if there is no values for any particular dimension then its not showning the dimension also . so , i have written if considition on the measure using isblank but i m getting error while using this if condition . could you help how to fix it . 

If dont mind can i have ur mobile number if u r from india . 

Dimension:

Account number, trascation number 

Measure :

EXPLANATION1_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=1)
EXPLANATION2_R=CALCULATE(LASTNONBLANK('TRANSACTION EXPLANATIONS'[EXPLANATION],1),'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER]=2)
 
the above measures might have null values for those dimensions . I want replace those null values with "N/A". 

Thanks,

[v-lid-msft]

Hi @Anonymous ,

We can using the following measure.

EXPLANATION1_R =
VAR result =
    CALCULATE (
        LASTNONBLANK ( 'TRANSACTION EXPLANATIONS'[EXPLANATION], 1 ),
        'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER] = 1
    )
RETURN
    IF ( ISBLANK ( result ), "N/A", result & "" )

EXPLANATION2_R =
VAR result =
    CALCULATE (
        LASTNONBLANK ( 'TRANSACTION EXPLANATIONS'[EXPLANATION], 2 ),
        'TRANSACTION EXPLANATIONS'[EXPLANATION_LINE_NUMBER] = 2
    )
RETURN
    IF ( ISBLANK ( result ), "N/A", result & "" )

We suggest you to open a new post for the different question to help other menbers find the similar question quickly.

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.

[Anonymous]

Thanks for respone

When i applied your logic but i am facing error 

i have mapped both the tables (transcations and transcation_explanation) on transcation number . But the data in the tables as like the transcation number is present trascantion table but the same number wont be there in the transcation_explanation table . The var is returning value but when we write the if condition i was facing above error .  

I have already opened a new post on this issue 

https://community.powerbi.com/t5/Desktop/columns-to-rows-in-direct-mode/m-p/795462#M382898

[Anonymous]

Thanks a lot .. its working .. 

[Anonymous]

canyou tell is my above dax query is correct .. i mean the filter considition part ..  

[Anonymous]

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 .