Forum Discussion

AmeenVanakar's avatar
AmeenVanakar
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Values in calculated column do not filter according to date selected - Need Help

Hi, I have created a calculated table to show unique customers (customer mobile number), their unique visits (number of unique invoices), and a group based on their number of visits.   I have also...
Need Help
_AAndrade's avatar
_AAndrade
Icon for Resident Rockstar rankResident Rockstar
1 year ago

Hi AmeenVanakar,

The problem is in your Customer Mobile Number Table, because you are using one table to classify the customer by the number of visits of all data.
For example, Customer 3165296135 had 2 unique invoices, so 2 visits classification but he had one Invoice on 28/06/2024 and other on 18/08/2024.
In your example you are using filters to "2 Visits" and one period between 01/08/2024 and 23/09/2024. Therefore, all your measures are rigth. The client had 2 visits (all time) and in the select period he had only one invoice.

What you want to have in the final output? The classification must be done depending also the selected period?

AmeenVanakar's avatar
AmeenVanakar
Icon for Advocate II rankAdvocate II
1 year ago

Hi _AAndrade 

Thanks for your update.

Customer 3165296135 should be excluded in this case under the column "Sum of Unique Invoices" as he/she had only 1 visit between 8/1/2024 to 9/23/2024. Is there a way to do this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ALL

    Firstly  _AAndrade thank you for you solution! 

    And AmeenVanakar ,As you said When Customer 3165296135 has only one visit in the selected date, we exclude him, that is, we show blank value, you can use the following DAX, which can help you to exclude Customer IDs that have only one visit record, I hope it will be helpful for you!

    Sum of Unique Invoices = 
CALCULATE(
    DISTINCTCOUNT(Customer_Data[BILL NO]),
    FILTER(
        VALUES(Customer_Data[CUSTOMER MOBILE NO]), 
        CALCULATE(
            DISTINCTCOUNT(Customer_Data[BILL NO]) > 1
        )
    )
)

     

    If you have further questions, you can contact me at any time, I will reply to you at the first time after I receive the message, looking forward to your reply!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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