topic Unique Distinct Count of Policy Numbers dependent on Other Column Criteria in DAX Commands and Tips

Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

Mikey575 — Tue, 17 Oct 2023 09:39:43 GMT

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?

Re: Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

DimaMD — Tue, 17 Oct 2023 12:48:28 GMT

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

Re: Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

Mikey575 — Tue, 17 Oct 2023 13:46:09 GMT

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

Re: Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

DimaMD — Wed, 18 Oct 2023 07:51:29 GMT

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

Re: Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

Mikey575 — Wed, 18 Oct 2023 08:21:59 GMT

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:

Re: Unique Distinct Count of Policy Numbers dependent on Other Column Criteria

DimaMD — Wed, 18 Oct 2023 12:19:49 GMT

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