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 ...
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.

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

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks a lot .. its working .. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    *** 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,

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    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