Forum Discussion

jmkvalsund's avatar
jmkvalsund
Helper III
2 years ago
Solved

Measure returning data only when using smaller than operator

There might be an obvious solution for this topic, but I just dont see it:

 

I have a datasource looking like this:

CustomerID

OrderDateID

CAM
12345620231215CAM1
12345720240120CAM2
12345820240115CAM1
12345920231227CAM3
12346020231120CAM1


Then I want my measure to count the number of customers having their first order placed this year, using the MIN-function on OrderDateID,saying it should be larger than the measure M_LastDatePreviousYear. And then filter by CAM (Customer Account Manager).
Should be easy, like this:

M_NumberOfNewCustomersByCAMThisYear =

//Converting measure to variable
// (M_LastDatePreviousYear = 20231229, tested using both dynamical and static value)

VAR var_M_LastDatePreviousYear = [M_LastDatePreviousYear] 

 

RETURN CALCULATE(DISTINCTCOUNT(Customers[CustomerID]),
FILTER(Customers,MIN(Customers[OrderDateID])>var_M_LastDatePreviousYear),
FILTER(Customers[CAM]=[M_SelectedUSer])
)


However, this always returns a blank measure.
But if I change the operator like this:
FILTER(Customers,MIN(Customers[CreatedDateID])<var_M_LastDatePreviousYear),

it returns the number of customers having their first order before M_LastDatePreviousYear

I can also insert a table visual, and display all customers having their first order this year in the way shown above. So there are customers fulfilling my spec, I just want to count them 🙂

Anyone who can enlighten me on this one?

Regards,

John Martin

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jmkvalsund ,

    Firstly thanks to Ashish_Mathur  and lbendlin  for their prompt replies, I created some sample data to solve the problem for you. You can follow the steps below:

    1. Modify your measure.

    M_NumberOfNewCustomersByCAMThisYear =
    VAR var_M_LastDatePreviousYear = [M_LastDatePreviousYear]
    VAR M_SelectedUser =
        SELECTEDVALUE ( Customers[CAM] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Customers[CustomerID] ),
            FILTER (
                Customers,
                CALCULATE ( MIN ( Customers[OrderDateID] ) ) > var_M_LastDatePreviousYear
            )
        )
    

    2.Add a Slicer.

     

    Final output:
    Count all CAM when OrderDateID > 20231229

    Count CAM1 when OrderDateID > 20231229

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Hi,

    Based on the data that you have shared in the blue table, show the expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi jmkvalsund ,

    Firstly thanks to Ashish_Mathur  and lbendlin  for their prompt replies, I created some sample data to solve the problem for you. You can follow the steps below:

    1. Modify your measure.

    M_NumberOfNewCustomersByCAMThisYear =
    VAR var_M_LastDatePreviousYear = [M_LastDatePreviousYear]
    VAR M_SelectedUser =
        SELECTEDVALUE ( Customers[CAM] )
    RETURN
        CALCULATE (
            DISTINCTCOUNT ( Customers[CustomerID] ),
            FILTER (
                Customers,
                CALCULATE ( MIN ( Customers[OrderDateID] ) ) > var_M_LastDatePreviousYear
            )
        )
    

    2.Add a Slicer.

     

    Final output:
    Count all CAM when OrderDateID > 20231229

    Count CAM1 when OrderDateID > 20231229

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Hi Anonymous ,

    Thanks, I've tried your approach here, and I see this might be another way to solve the issue.
    I can replicate the table and get the total numbers of new customers as total. 
    However, if I then try to put M_NumberOfNewCustomersByCAMThisYear onto a card, it still says "(blank)"...
    What's your properties on the card showing M_NumberOfNewCustomersByCAMThisYear?

    Regards,
    John Martin

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi jmkvalsund ,

      You need to change :

      MIN(Customers[OrderDateID])>var_M_LastDatePreviousYear

      to :

      CALCULATE ( MIN ( Customers[OrderDateID] ) ) > var_M_LastDatePreviousYear

      in order for the card to output the correct result.

      The card shows a total greater than the date 20231229.

       

      How to Get Your Question Answered Quickly - Microsoft Fabric Community

      If it does not help, please provide more details with your desired out put and pbix file without privacy information.

       

      Best Regards,

      Ada Wang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.