Forum Discussion

GianPaolo's avatar
GianPaolo
Frequent Visitor
4 years ago
Solved

DISTINCTCOUNT on factTable based on 2 Criteria

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

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago
    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))
    • GianPaolo's avatar
      GianPaolo
      Frequent Visitor
      4 years ago

      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!