Forum Discussion

jacob2102's avatar
jacob2102
Icon for Helper II rankHelper II
5 years ago
Solved

CountX or other DAX expression

Hi,

 

I have a data table with different publications and the participating groups in each publication.

PublicationGroup
P1a
P1b
P1c
P2a
P2c
P3b
P4a
P4b
P4c
P4d
P5d

 

If I want to count the number of publications that has 3 or more participating groups, what is the correct DAX expression?

At this moment, I have tried this expression "countx(filter(table,count(table[group])>3),table[publication])", but the resoult is not correct, the resoult should be 2 not 11.

 

How can I solve it? What is the correct DAX expression?

 

Thanks,

Jacob

  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    jacob2102 

    this code could be work

    PublicationCount:=COUNTROWS(FILTER(ALL(Table1[Publication]),CALCULATE(DISTINCTCOUNT(Table1[Group]),ALLEXCEPT(Table1,Table1[Publication]))>2))

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, jacob2102 , you might want to try this measure without COUNTX but with COUNTDISTINCT instead,

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Publication] ),
    FILTER (
        ALL ( 'Table'[Publication] ),
        CALCULATE ( DISTINCTCOUNT ( 'Table'[Group] ) >= 3 )
    )
)
    • jacob2102's avatar
      jacob2102
      Icon for Helper II rankHelper II
      5 years ago

      Hi,

       

      Thanks for your answer, but with this measere I get a blank result.

      Maybe I can use another measure?

       

      Thanks.

      Jacob

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jacob2102 

    this code could be work

    PublicationCount:=COUNTROWS(FILTER(ALL(Table1[Publication]),CALCULATE(DISTINCTCOUNT(Table1[Group]),ALLEXCEPT(Table1,Table1[Publication]))>2))
  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jacob2102 ,

     

    Try this measure:

     

    _Count = COUNTX(FILTER(SUMMARIZE('Table', 'Table'[Publication], "Count", DISTINCTCOUNT('Table'[Group])), [Count] >= 3), [Count])

     

    Or this one:

    _Count = COUNTX(FILTER(ADDCOLUMNS(VALUES('Table'[Publication]), "Count", CALCULATE(DISTINCTCOUNT('Table'[Group]))), [Count] >= 3), [Count])