Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

measure

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??

 

 

DateCustomer IDQuantity
January115
January2210
January114
January337
February119
February553
February112
February113
March774
March997
March999
March773

 

Thank you!!!

 

 

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

You can use this MEASURE.

File attached as well

 

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

 

measure.png


Regards
Zubair

Please try my custom visuals

View solution in original post

@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

 

meaure.png

 

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
)

 


Regards
Zubair

Please try my custom visuals

View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

You can use this MEASURE.

File attached as well

 

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

 

measure.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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

Hi @Anonymous,

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

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

 

meaure.png

 

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
)

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.