topic Re: Showing Unique values based on multiple criteria in DAX Commands and Tips

Showing Unique values based on multiple criteria

nikki11 — Mon, 27 Nov 2023 11:00:51 GMT

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:

Client	Assignment	Value
CLIENTA	ASSIGNA	10
CLIENTA	ASSIGNB	5
CLIENTA	ASSIGNC	2
CLIENTB	ASSIGNC	10
CLIENTC	ASSIGNB	2
CLIENTD	ASSIGNA	5
CLIENTE	ASSIGNA	15
CLIENTE	ASSIGNB	5
CLIENTE	ASSIGNC	20
CLIENTF	ASSIGNA	3

Re: Showing Unique values based on multiple criteria

Anonymous — Mon, 27 Nov 2023 11:48:01 GMT

Hi nikki11 ,

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

Re: Showing Unique values based on multiple criteria

nikki11 — Mon, 27 Nov 2023 13:09:59 GMT

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

Re: Showing Unique values based on multiple criteria

nikki11 — Mon, 27 Nov 2023 13:11:24 GMT

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

Re: Showing Unique values based on multiple criteria

Anonymous — Thu, 30 Nov 2023 02:24:11 GMT

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