DAX - Perform filter on table based on row counts

[al4x]

Hi,

Having only just gotten my head around how CALCULATE filter i have a question on how do best achieve what i want.

I have a data table in the form

Cat1, Cat2, Country, Data

B,Z,France,10

B,Y,France,20

B,X,France,10

C,Z,France,20

C,Y,France,20

...

Where Cat1 can only contain B and C but rest are free, and so is the table length.

I want to only display an AVERAGE of [Data] for B

when the DISTINCTCOUNT of [Cat2] where [Cat1] = B

is 1 GREATER THAN

the DISTINCTCOUNT of [Cat2] where [Cat1] = C

But always display AVERAGE of [Data] for C

This is displayed on a bar plot

ie in the demo table, only a bar for AVERGAE [Data] for 'C' would be displayed as the DISTINCTCOUNT of  B for Cat2 is 3

vs the DISTINCTCOUNT of  C for Cat2 is 2

and 3-2 = 1

Is the best way to do this with varibles? for example s variables in the form of the below would give me the two numbers

    CALCULATE(
        DISTINCTCOUNT(Dat_Table[cat2]),
        FILTER(
            Dat_Table,
            Dat_Table[cat1] = "B"
        )
    )

or is there a better way?

I should add that this table has a number of filters attached to it and the above scenario needs to operate from the ouput of these.

[v-yulgu-msft · Accepted solution]

Hi @al4x,

Please try this meaure:

Average =
IF (
    LASTNONBLANK ( Dat_Table[Cat1], 1 ) = "B",
    IF (
        CALCULATE (
            DISTINCTCOUNT ( Dat_Table[Cat2] ),
            FILTER ( ALL ( Dat_Table ), Dat_Table[Cat1] = "B" )
        )
            - CALCULATE (
                DISTINCTCOUNT ( Dat_Table[Cat2] ),
                FILTER ( ALL ( Dat_Table ), Dat_Table[Cat1] = "C" )
            )
            >= 2,
        CALCULATE (
            AVERAGE ( Dat_Table[Data] ),
            FILTER ( Dat_Table, Dat_Table[Cat1] = "B" )
        ),
        BLANK ()
    ),
    CALCULATE (
        AVERAGE ( Dat_Table[Data] ),
        FILTER ( Dat_Table, Dat_Table[Cat1] = "C" )
    )
)

Best regards,

Yuliana Gu