Forum Discussion

sureshsay3's avatar
sureshsay3
New Member
4 years ago
Solved

Measure to fetch data from table

Hi All,

 

I have a table data like below

Idstatus
1Missing
1Yes
1No
2Yes
2NO
3

Yes

 

I need a dax function to calculate unique id where we do not have Missing value in status. 

Result:

count= 2

 

measure = calculate(distinctcount(id),filter(table, Status<>"Missing") gives value of 3. 

Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    sureshsay3 Maybe:

    Measure =
  VAR __Missing = DISTINCT(SELECTCOLUMNS(FILTER('Table',[status]="Missing"),"Id",[Id]))
  VAR __All = DISTINCT('Table'[Id])
  VAR __Table = EXCEPT(__All,__Missing)
RETURN
  COUNTROWS(__Table)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    sureshsay3 Maybe:

    Measure =
  VAR __Missing = DISTINCT(SELECTCOLUMNS(FILTER('Table',[status]="Missing"),"Id",[Id]))
  VAR __All = DISTINCT('Table'[Id])
  VAR __Table = EXCEPT(__All,__Missing)
RETURN
  COUNTROWS(__Table)
    • sureshsay3's avatar
      sureshsay3
      New Member
      4 years ago

      Hi Greg_Deckler ,

       

      I am getting a value near the expected value for my report. i have to add additional filters based on another table. Will try those and hopefully it all works.

       

      Regards,

      Suresh

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        4 years ago

        As an alternative you could try:

        distinctcount <> Missing =

        VAR _Missing = CALCULATE (DISTINCTCOUNT (Table [ID]), Table [Status] = "Missing")

        VAR _All = DISTINCTCOUNT(Table [ID])

        RETURN

        _All  -  _Missing