Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure for selected value

Hi, 

I am working on a report where ask is to compare entity and its peer data on the visuals. this report should be RLS enabled but need to show the aggregated data for all the peer when user see the report. So, I have created a separate table to bring all the peer's data and keep it out of RLS and created an inactive relationship with the main table.

Now on the report, At first level, user passes an entity number through slicer and based on this entity number calculate some measures. One measure should count the values for all the entities except the selected entity number, so we can get the count for selected entity's peer.

the other measure should count the values based on the second level filter by providning value for industry, revenue and state and should follow the above rule where we have to count for all the entities except the passed entity number.

 

 

 

Please suggest how to achieve this.

 

Thanks

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Would need to understand the data better but sounds like you want to do something like FILTER(ALL('

    Table', [Column] <> SELECTEDVALUE('Table1'[Column1]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Greg_Deckler  I am using the below dax to calculate peer count

     

    Peer Count =
    var Entityno. = SELECTEDVALUE('Main View'[Entity Number],BLANK())
    var p1 = CALCULATE(COUNT('Secondary View'[ID])
    ,'Seconday View'[Entity Number] <> Entityno.
    , USERELATIONSHIP('Main View'[Entity Number]
    ,'Seconday View'[Entity Number])
    )
    Return
    P1
     
    I am struggling when trying to compare entity and it's peer count on a single visual using a parameter on axis because both the views/tables do not have the active relationship. 
    Please suggest how to overcome this issue.
     
    Thanks
    • v-janeyg-msft's avatar
      v-janeyg-msft
      Community Support
      4 years ago

      Hi, Anonymous 

       

      What's your question? What's the difference between your code and your requirement result?

      In your code, I don't think you need 'USERELATIONSHIP('Main View'[Entity Number], 'Seconday View'[Entity Number])' , Removing this code should have no effect on the result.

      It is difficult to help you without knowing your needs and context. If you still need help, please add more details or sample data and feel free to ask me.

       

      Best Regards,

      Community Support Team _Janey

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly