Forum Discussion

yishenhui's avatar
yishenhui
Helper II
3 years ago
Solved

distinctcount and countx

Hi, 

 

I'd like to have a dax like distinctcount and countx. discount a table/column with filter. 

 

sample table as below left, I have created a measure m1  (m1 = DISTINCTCOUNT(table1[c1])) and it's able to show in the table visual with a filter of c2. I want to show another column with the measure m2 with all distinctcount(table[c1]) without any filter from c2. I try to create one but got the error message.  How can I get it?

 

 

Thanks

 

  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    yishenhui Try:

    m2 = 
  VAR __c3Table = SELECTECOLUMNS( 'table1', "c3", [c3] )
  VAR __Result = 
    COUNTROWS( 
      DISTINCT( 
        SELECTCOLUMNS( 
          FILTER( ALL('table1'), [c3] IN __c3Table ),
          "c1", [c1] 
        ) 
      ) 
    )
RETURN
  __Result

4 Replies

    • yishenhui's avatar
      yishenhui
      Helper II
      3 years ago

      Hi Greg,

       

      It did work. I will accept as solution. I also added another column and some data as below. c3 will be a column in the table visual. The second column shows all counts as before. can I have the DAX that show the distcount with filter c3 but without c2? I tried write another DAX M3 but looks like it cannot see [c3].

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        yishenhui Try:

        m2 = 
  VAR __c3Table = SELECTECOLUMNS( 'table1', "c3", [c3] )
  VAR __Result = 
    COUNTROWS( 
      DISTINCT( 
        SELECTCOLUMNS( 
          FILTER( ALL('table1'), [c3] IN __c3Table ),
          "c1", [c1] 
        ) 
      ) 
    )
RETURN
  __Result