Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

[Mikey575]

Hi

I am fairly new to Power BI and DAX. I am currently trying to move a report from Qlikview to Power BI and one of the formulas I need to replicate uses qlikview functions P() & E() that dont seem to be available in Power BI but i am sure DAX could replicate in some way. I have spent some time looking on this forum but any answers that seem to tackle the issue dont work when I apply them.

In the example below i have a list of policy numbers and products and i only want to count the products where they exist on their own. E.g. I want the count of policy numbers for the Product called ISA where the corresponding policy number doesnt have another Product attached to it. The number of ISAs in the list below is 4 but the result i want based on the criteria above is 2.

If we were to replicate this for the Product SIPP then the number of SIPPs in the list is 5 but the result i want is 3.

If again we were to replicate this for the Product Cash then the number of Cash in the list is 3 but the result i want is 1.

I have colour coded what needs to be counted with the results in the same colour 

Does anyone one have a DAX formula that would do this?

[Mikey575]

Thanks @DimaMD how would I code it so I isolate just 1 product, say ISA? 

[DimaMD]

@Mikey575  Hi, Specify your question, what exactly do you want to see?

[Mikey575]

Hi DimaMD

So your solution brings back the correct result. However i would like to modify it so it only brings back the Count for the Product ISA. See Below:

[DimaMD]

@Mikey575 You have two options to add a slicer with products or to add filtering by product

In my opinion, it is better to add a slicer to the report and you will be able to see the quantity for any product

[DimaMD · Accepted solution]

Hi @Mikey575  try it measure

Product_count = 
VAR coun = 
ADDCOLUMNS(
    'table', "counta", 
    CALCULATE( 
        COUNT([Policy Number]), 
        FILTER( ALL('table'), [Policy Number] = EARLIER([Policy Number])
        )
    )
)
VAR result = CALCULATE( COUNT('table'[Product]), FILTER( coun, [counta] = 1))
return
result