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

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