Forum Discussion

LS1700's avatar
LS1700
Icon for Resolver I rankResolver I
9 years ago
Solved

Summarize with filters

Hello,   I have this table, and I want to create a measure that can tells me how many customers have bought at least one unit, in a specific point in time. Basically, I want to have a filters on m...
Need Help
  • LS1700's avatar
    LS1700
    9 years ago

    Well, I think I found the solution:

     

     

    # of dealers = CALCULATE(DISTINCTCOUNT([Customer]),filter(ALLEXCEPT(Sheet1,Sheet4[country]),Sheet1[sales]>0),FILTER(ALLEXCEPT(Sheet1,Sheet4[country]),Sheet1[Date]=Max(Sheet1[Date])))

    regards

     

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi LS1700,

 

The filters interact with each other. We can use "Edit interactions" to control them. You can set them with step 1 and 2 in the image. You can see the result with step 3 and 4.

Maybe this measure is better.

Active Customers =
CALCULATE (
    DISTINCTCOUNT ( Table1[Customer] ),
    'Table1'[Sales] > 0,
    ALLEXCEPT ( Table1, Table1[Country], Table1[Date] )
)

 

 

LS1700's avatar
LS1700
Icon for Resolver I rankResolver I
9 years ago
Hi, I ll give it a Try to your formula.

I had the very same idea for the interactions, but the problem is that I need the results of the measure to calculate another value.

Yes... Not simple :)
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi, LS1700

     

    With this measure I just posted, the result will only be filtered by "country" and "date". So other slicer like "Segment" will not affect the result. The slicer only hide or show the result, but not recalculate it now.

    Active Customers =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Customer] ),
        'Table1'[Sales] > 0,
        ALLEXCEPT ( Table1, Table1[Country], Table1[Date] )

    )

     

    Best regards
    Dale

    • LS1700's avatar
      LS1700
      Icon for Resolver I rankResolver I
      9 years ago

      Hi,

      thanks for your suggestion.

       

      I feel it is very close to what I am looking for. Unfortunately it does not entirely fulfil what I am looking for.

       

       

      the porblem is with the date: my date "filter" is a slicer, I use this formula to filteron the largest date selcted within the slicer:

      filter(all(Sheet1[Month Date]), Sheet1[Month Date]=Max(Sheet1[Month Date])))

       

      the problem is that if I add it to your formula it will start to ingnore the "ALLEXCEPT".

       

      I have been looking to other posts on the forum, it seems the "FILTER" function removes the condition "ALLEXPECT"

      https://community.powerbi.com/t5/Desktop/Filter-and-Allexcept/m-p/87832

       

       

       any idea?

       

      • LS1700's avatar
        LS1700
        Icon for Resolver I rankResolver I
        9 years ago

        Well, I think I found the solution:

         

         

        # of dealers = CALCULATE(DISTINCTCOUNT([Customer]),filter(ALLEXCEPT(Sheet1,Sheet4[country]),Sheet1[sales]>0),FILTER(ALLEXCEPT(Sheet1,Sheet4[country]),Sheet1[Date]=Max(Sheet1[Date])))

        regards