Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DISTINCTCOUNT with condition

I have data as shown belew    id stage status 1 a complete 1 b complete 1 b not complete 2 a complete 2 b not complete   I want to count number of id based on not...
DAX
Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    I think this will work but please test properly with your data:

    MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
           VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN 
    COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))

    Each of the variables holds a table with the id and stage of the different statuses.

    Count the 'not completed' rows. Subtract from this the count  (of matching id and stage) rows that have a 'complete' and 'not complete'  status.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

COUNTROWS(

  SUMMARIZE(

     FILTER(data,data[status]="not complete"),

    [id]

  )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    thank you 
    but it is still the same , i need it first before count to check if the id with same stage have status complete if yes then, do not count it 
    if NO, count it.

    i need to do this becouse there is a duplicate on id with same stage , so to solve it i have to ignore the id on not complet if it complet with same stage.

    hope it is clear

    • HotChilli's avatar
      HotChilli
      Icon for Community Champion rankCommunity Champion
      6 years ago

      I think this will work but please test properly with your data:

      MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
           VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN 
    COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))

      Each of the variables holds a table with the id and stage of the different statuses.

      Count the 'not completed' rows. Subtract from this the count  (of matching id and stage) rows that have a 'complete' and 'not complete'  status.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        thank you it is working