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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AmeenVanakar
Helper II
Helper II

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 created a separate calculated table to show unique invoices (unique bill numbers), Bill Value ($ value of the invoice), and a group to define the bill range (by $ value of the bill)

 

The problem I face:

Upon filtering the data, the count/sum of unique invoice don't seem to match (they match when the calendar filter is removed) as sown below. How can I ensure that both the numbers match when I filter the data for a particular time period + bill value range + # visits. In the sample below I have filtered data to show the differences I see.

 

Here is the  dummy file.

Analysis (1).pbix

 

AmeenVanakar_0-1728465128636.png

 

1 ACCEPTED SOLUTION

Hi @AmeenVanakar.,

Here is my solution.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

11 REPLIES 11
AmeenVanakar
Helper II
Helper II

Hi @v-xingshen-msft & @_AAndrade 

Many thanks for helping with my issue so far.

Unfortunately, this still does not solve the problem.

I tried to replicate the expected outcome in Excel & found anomalies. Here is a scenario

 

1. Customer 334434424566 has 3 unique bills between 1st Sep 2024 to 30th Sept 2024. however, these 3 bills are all on the same day & not different days. Hence the visit filter should show "1 Visit" but instead it's showing "3 visits"

2. During these 3 visits, the customer had different bill values matching different "bill range" criteria. Hence when we filter for visits + bill range the numbers don't show accurate display of the data.

 

Solution required:

1. Is there a way to group these 3 bills from customer 334434424566 & show as a sum within the same date? 

2. The sum of bill numbers should be dynamic & change according to the date period & visits range selected.

3. Visit range should combine all bills within the same day as one & show as "1 visit" as on that day. This should also be dynamic & show a sum of all visits when a larger time period is selcted & link to visits filter.

 

AmeenVanakar_3-1728548159781.png

AmeenVanakar_0-1728551362498.png

 

Here is the link to excel file & the updated .pbix report shared by @_AAndrade .

Excel File Customer Billing Data_workings.xlsx

.pbix file Analysis_DinRange.pbix

Hi @AmeenVanakar,

I did some changes on my first pbix file and I think now is working as you want. For example, on 04/05/2023 we have 3 rows without customer number so the old dax formula will count 3 visits but with the new one is counting only one visit.
Take a look at my new pbix file.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Hi @_AAndrade ,

The calculations work very well with the measures you've created 👍. However, we need to bring this all together in a column format along with bill range (bill value) so we can use it as a filter at the dashboard level, only then will we be able to run the analysis accurately.

 

Is there a way to use the measures as filters?

I didn't understand your point.
What is wrong on my pbix file? I have one filter with visits and have your first filter with bill classification.
What is not working properly?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Can you help me with the bill range filter? the current filter doesn't filter the 'Total Sales Final' values within the specified range.

 

Analysis_DinRangeV2.pbix

 

AmeenVanakar_0-1728572241364.png

 

Hi @AmeenVanakar.,

Here is my solution.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Hi @_AAndrade 

Thanks a ton! this works well now.

_AAndrade
Super User
Super User

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?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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?

Hi @AmeenVanakar,

I'm attaching a pbix file with my solution.
Any doubt please let me know.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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

 

vxingshenmsft_0-1728541029925.png

vxingshenmsft_1-1728541258687.png

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.

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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