measure

[Anonymous]

Hi Community!!

i am facing a problem in a DAX measure that i cant solve days now.

I have a sales table which in the first column is the date, in the 2nd column is the customer ID and in the last column i have the quantities sold. My question is: how can i calculate the number of unique customers (Customer ID) sell less than 10 quantities in February of 2018? (in the example below the answer is 1 Customer)

Could you please be so kind to help me??

Date	Customer ID	Quantity
January	11	5
January	22	10
January	11	4
January	33	7
February	11	9
February	55	3
February	11	2
February	11	3
March	77	4
March	99	7
March	99	9
March	77	3

Thank you!!!

[Zubair_Muhammad · Accepted solution]

@Anonymous

You can use this MEASURE.

File attached as well

Measure =
COUNTX (
    FILTER (
        VALUES ( Table1[Customer ID] ),
        CALCULATE ( SUM ( Table1[Quantity] ) ) < 10
    ),
    1
)

[Zubair_Muhammad · Accepted solution]

@Anonymous

You can use this MEASURE.

File attached as well

Measure =
COUNTX (
    FILTER (
        VALUES ( Table1[Customer ID] ),
        CALCULATE ( SUM ( Table1[Quantity] ) ) < 10
    ),
    1
)

[Anonymous]

Thank you very much for the fast response, works great!!

[Anonymous]

Can we create a measure that shows only the results of February?

[v-danhe-msft]

Hi @Anonymous,

You could use the slicer to choose the value you want to show:

Regards,

Daniel He

[Zubair_Muhammad · Accepted solution]

@Anonymous

It depends on how and where you want to show the results

As Daniel pointed out you could use a slicer

Also

1) you can use a VISUAL level filter for Month

2) If you want all other months to show value of February you can use

Measure 2=
COUNTX (
    CALCULATETABLE (
        FILTER (
            VALUES ( Table1[Customer ID] ),
            CALCULATE ( SUM ( Table1[Quantity] ) ) < 10
        ),
        Table1[Date] = "February"
    ),
    1
)

3) If you want all other months to show null you can use

Measure 3 =
COUNTX (
    CALCULATETABLE (
        FILTER (
            VALUES ( Table1[Customer ID] ),
            CALCULATE ( SUM ( Table1[Quantity] ) ) < 10
        ),
        KEEPFILTERS ( Table1[Date] = "February" )
    ),
    1
)

[Anonymous]

Thank you very much!! I needed the dax calculation to show only February.