Forum Discussion

yishenhui's avatar
yishenhui
Icon for Helper II rankHelper 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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    yishenhui Try:

    m2 = COUNTROWS( DISTINCT( SELECTCOLUMNS( ALL('table1'), "c1", [c1] ) ) )
  • 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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

yishenhui Try:

m2 = COUNTROWS( DISTINCT( SELECTCOLUMNS( ALL('table1'), "c1", [c1] ) ) )
yishenhui's avatar
yishenhui
Icon for Helper II rankHelper 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
    Icon for Community Champion rankCommunity 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
    • yishenhui's avatar
      yishenhui
      Icon for Helper II rankHelper II
      3 years ago

      Hi Greg,

       

      I found another issue. Here assume c3 is always existed in all selection of c2 with is good. But if I change the data a little bit, replaced one "c" to to "b" (hightlighted). Then when I choose the "c" in the slicer, because "c" is nolonger existed with any rows having ca, the result table doesn't have record for ca any more. Is it possible even the selection doesn't exist in that group, the total count still showing. Means in this case, for row ca, m1 will be 0 and m3 will continue show 2?