Forum Discussion

al4x's avatar
al4x
Frequent Visitor
8 years ago
Solved

DAX - Perform filter on table based on row counts

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.

  • 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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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