CountX or other DAX expression

[jacob2102]

Hi,

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

Publication	Group
P1	a
P1	b
P1	c
P2	a
P2	c
P3	b
P4	a
P4	b
P4	c
P4	d
P5	d

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 · Accepted solution]

@jacob2102 

this code could be work

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

[CNENFRNL]

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]

Hi,

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

Maybe I can use another measure?

Thanks.

Jacob

[camargos88]

@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])