Distinct Count of a unique ID with no intersect of 2 conditions

[sperry]

I am sure this is easy but can't work it out;

I have a data set where multiple rows exists for a unique ID where an answer to many questions can be recorded.

Simplified sample below;

UniqueID    Question1  Question2

A1               Ticked

A2               Ticked

A3               Ticked

A3                                  Ticked

A4               Ticked

I need a measure to create a distinct count of UniqueIDs where there exists a row for Question1="Ticked" but there are no instances of Question 2="Ticked" for that UniqueID. So the result for the above should be 3 as UniqueID = A3 is excluded due to a row where Question2= "Ticked" exists.

In reality I will have an expanding list of queries based on many answers to many questions but I'm sure if I have a solution to the above I can extend the logic to cover multiple criteria.

[sperry · Accepted solution]

Thanks so much..I have remodelled my data and although I will have to re-work 50 measures I've written ('referenced' the original query for the puposes of accomodating your solution in the meantime) it will be miles better..Leant way more than I thought I would.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sperry]

Thanks for getting back so quickly..I think you have re-framed the data somehow as it looks like you have pivoted so only one column exists for the questions whereas in reality I have around 40 columns to build the measures on.

[Ashish_Mathur]

Hi,

By restructuring your dataset, i have made it easier for you to write measures.

[sperry]

Excellent..but are you saying there is no way to write a measure based on my current dataset? I already have around 50 measures written on the way I receive the data. looks like I need another post/investigation on re-modelling as I do not know this right now.

[sperry]

Excellent..but are you saying there is no way to write a measure based on my current dataset? I already have around 50 measures written on the way I receive the data. looks like I need another post/investigation on re-modelling as I do not know this right now.

[Ashish_Mathur]

Hi,

This is the best data structure to maintain - a flat structure as opposed to a Pivot structure.  You will benefit from this structure in the long run.  In your current data structure, if you insist, i can try to write a measure to get the answer.

[sperry]

That would be great if you could..I will work on remodelling the data in the meantime

[sperry · Accepted solution]

Thanks so much..I have remodelled my data and although I will have to re-work 50 measures I've written ('referenced' the original query for the puposes of accomodating your solution in the meantime) it will be miles better..Leant way more than I thought I would.

[sperry]

Sorry I have just encountered an issue that I can't resolve and I thought I would be able to.

I have unpivoted the data which has resulted in a single column containing all the 'Questions' (of which there are 20) and a single column with the corresponding values.

I need to take the conditions described above (ie. distinct count the Unique Identifier for the existence of question 1 where there does not exist a value for question 2) and overlay where more that 2 questions are present in the data.

Does this make sense?

[Ashish_Mathur]

Share a dataset and clearly show the expected result.

[sperry]

here is link to an annonmysied data set.

PBI Test.xlsx

So I specifically need a count of distinct UniqueIDs which have Measure=SMPREADYFORGP9 with a Value= "Ticked" but only if there are no instances for that UniqueID of a Measure = SMPCOMPLETED10 with a value = "Ticked"

[sperry]

And the expected result is 6 - see the pivots

[sperry · Accepted solution]

Count of GP Ready NHI = COUNTROWS(FILTER(SUMMARIZE(DISTINCT(RespiratoryMngmtFlat[NHI]),RespiratoryMngmtFlat[NHI],"ABCD",CALCULATE(COUNTROWS(RespiratoryMngmtFlat),RespiratoryMngmtFlat[Measure]="SMPREADYFORGP9",RespiratoryMngmtFlat[Value]="Ticked"),"EFGH",CALCULATE(COUNTROWS(RespiratoryMngmtFlat),RespiratoryMngmtFlat[Measure]="SMPCOMPLETED10",RespiratoryMngmtFlat[Value]="Ticked")),[ABCD]>0&&[EFGH]=0))

Ha I solved it..

Simples..Thanks for your patience!

[Ashish_Mathur]

You are welcome.