topic Re: DISTINCTCOUNT on factTable based on 2 Criteria in DAX Commands and Tips

DISTINCTCOUNT on factTable based on 2 Criteria

GianPaolo — Mon, 06 Jun 2022 14:26:29 GMT

Hi Everyone!

I am quite new to DAX. I made my model really simple in the attached file .xlsx

I would like to compute a DISTINCTCOUNT of the FACT_TABLE[CUSTOMER ID] based on the following criteria:

Total Values for each "Customer ID" in the "1 SEMESTER" = 0 AND Total Values for each "Customer ID" in the 2 SEMESTER > 0

In this example CUSTOMER ID = 1 AND CUSTOMER ID = 3 are ok and they should be counted in the DISTINCTCOUNT because both have 1 semester = 0 and 2 semester > 0

https://filetransfer.io/data-package/47cqvbpL#link

thanks in advance

Re: DISTINCTCOUNT on factTable based on 2 Criteria

johnt75 — Mon, 06 Jun 2022 15:17:15 GMT

Num customers = var semester1 = CALCULATETABLE( VALUES('Table'[Customer ID]), TREATAS( { ("Semester 1", 0)}, 'Table'[Semester], 'Table'[Values Euro]) ) var semester2 = CALCULATETABLE( VALUES('Table'[Customer ID]), 'Table'[Semester] = "Semester 2" && 'Table'[Values Euro] > 0) return DISTINCTCOUNT(UNION(semester1, semester2))

Re: DISTINCTCOUNT on factTable based on 2 Criteria

CNENFRNL — Mon, 06 Jun 2022 21:18:18 GMT

For fun only, a showcase of powerful Excel worksheet formula,

Re: DISTINCTCOUNT on factTable based on 2 Criteria

GianPaolo — Tue, 07 Jun 2022 09:19:40 GMT

Thank you for your answer!! Unofortunately I am using Power Pivot and the TREATAS is not provided....

I think it is really a good idea the Excel flag you did...

DO you think would be possibile to add a calculated column in the model for doing the same thing as you did in the Excel Worksheet?

Thanks a lot!