Forum Discussion

nikki11's avatar
nikki11
Regular Visitor
2 years ago
Solved

Showing Unique values based on multiple criteria

I am currently trying to create a visulisation where there are unique occurances in my data.

 

So in the example below, the visual would be filtered only showing clients that have one assignment associated to them, in the example below it would therefore only show ClientB, ClientC, ClientD and ClientF on my visualisation.

 

I am sorry to trouble you, im new to PBI and DAX and am wondering what is the best way to do this.

 

I tried using a DISTINCOUNT but amn not getting the right outcome on the filter (ideally want to filter on the total value and not the individual counts).

 

Your help would be very much appreciated.  Thanks

 

For example:

ClientAssignmentValue
CLIENTAASSIGNA10
CLIENTAASSIGNB5
CLIENTAASSIGNC2
CLIENTBASSIGNC10
CLIENTCASSIGNB2
CLIENTDASSIGNA5
CLIENTEASSIGNA15
CLIENTEASSIGNB5
CLIENTEASSIGNC20
CLIENTFASSIGNA3
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi nikki11 ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a measure as below

    Flag = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Client],
        "@assign",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Assignment] ),
                ALLEXCEPT ( 'Table', 'Table'[Client] )
            )
    )
RETURN
    IF ( SUMX ( _tab, [@assign] ) = 1, 1, 0 )

    2. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

    Best Regards

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nikki11 ,

     

    Create a new measure as below.. and apply filter in the slicer visual.

     

  • nikki11's avatar
    nikki11
    Regular Visitor
    2 years ago

    Thank you for your help.  I have tried this and I think I may have explained incorrectly what I am trying to achieve as it doesn't seem to give me the desired outcome.

     

    Scenario

    Table 1 - List client information, including Client Name

    Table 2 - lists assignment information, for example total time logged against each assignment, value of that time

     

    Table 1 would list all clients and there would only be 1 dataset per client.  However, the Assignment table my include many input data lines for many clients, so for example over a yearly period you could have hundreds of lines entered for various assignments for varying clients

     

    So a one to many relationship.

     

    The visualisation wants to identify where a client is only engaged for one type of assignment.  Many of our clients will have multiple assignments.  But some of our clients only engage us for one particular assignment.

     

    We want to identify the clients that only engage us for one service and then calculate the value of that service.

     

    I tried to apply a distinct count  which worked in that it showed the clients with only 1 assignment, but when I tried to apply a visual filter, it would filter on the "total=1".

     

    Sorry to be such a nuisance and I greatly appreciate your help.

     

    Thanks

  • nikki11's avatar
    nikki11
    Regular Visitor
    2 years ago

    Sorry, I meant to say "it wouldn't filter on the "Total=1".

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi nikki11 ,

      I created a sample pbix file(see the attachment), please check if that is what you want.

      1. Create a measure as below

      Flag = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Client],
        "@assign",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Assignment] ),
                ALLEXCEPT ( 'Table', 'Table'[Client] )
            )
    )
RETURN
    IF ( SUMX ( _tab, [@assign] ) = 1, 1, 0 )

      2. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

      Best Regards