cancel
Showing results for
Did you mean:
Frequent Visitor

## Working with large data, dynamic filter to select value on specific date

Hi!
I am hoping someone has an easier solution.

I have a list of about 8m unique "buy numbers" with their current value and some details about the buy (clients, owner, etc.)

Then I have a list of each of the buy's history values

 Buy number Value 1 0 2 7 3 5 4 etc

 BuyNumber Date Changed Value 1 15 Jan 2023 6 1 23 Jan 2023 12 1 30 Jan 2023 0 2 2 Feb 2023 7 3 25 Jan 2023 11 3 3 Feb 2023 5

I need to build a report to show what buy's value was on specific dates (selected by 4 different date slicers) to get the below output:

 Buy number Client Slicer 1:23 January Slicer 2:1 February Slicer 3:2 February Slicer 4:3 February 1 Client A 12 0 0 0 2 Client A does not exist = 0 does not exist = 0 7 7 3 Client B does not exist = 0 11 11 5 Total: 12 11 18 12

At first I tried to write measures for each date, to pick up the value on the 4 slicer selected values, and it works for small filtered data, but proivdes an error "Visual has exceeded the available resources" when working over all the 8m buy numbers - which I understand.

My next thought was to use Generate and Summarize, to generate a table for eachbuy at the 4 slicer values and calculating the value using measures or calculated columns.

But this seems to take a long time to generate across the timeframe (about 12 years, which we can shorten to 4/5 years if needed)

Is there an easier way to do this that would allow me to obtain the values quickly and also be able to summarise the above table by client if needed?

The measure I used to write for Slicer 1 (BillRun 1) that works on a smaller scale of the data:

______________________________________________________________________
ValueSlicer1 =

RETURN
calculate(

___________________________________________________________________________
The table I tried to generate:

BR1 = generate(
filter(
),
Filter(
)

1 ACCEPTED SOLUTION
Community Support

You can follow the steps below to get it:

1. Create a date dimension table and apply the [Date] field on the slicer

2. Create a measure as below

``````Measure =
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
VAR _amenddate =
CALCULATE (
)
RETURN
SUMX (
FILTER (
&& [Value] <> 0
),
[Value]
)``````

If the above one can't help you, please provide more raw data in your table  'Buy' and 'BuyHistory' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Frequent Visitor

Thank you Rena!!
This seems to be working, still need to test it with the full population.

I did however need to change the formula to say = _amenddate (instead of < _amenddate)

Two questions if you don't mind a follow up?

1.  I noticed you used SUMX(..., FILTER(....)) where I used CALCULTE(SUMX(....)), what is the reason for this?

2.  The above measure provides the correct values on the 4 different dates.  However the columns are not adding correctly:

Is there another way of amending this than to create another measure as follows to correct the totals:

Thank you!

Community Support

Thanks for your feedback. I think the problem may not be that you are using calculate(sumx(), it may be some other calculation that is not returning the correct result. You can break your formula into multiple steps to verify each step and see if you get the desired result at each step.

In addition, you can refer the following links to resolve the incorrect total values problem.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

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

You can follow the steps below to get it:

1. Create a date dimension table and apply the [Date] field on the slicer

2. Create a measure as below

``````Measure =
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
VAR _amenddate =
CALCULATE (
)
RETURN
SUMX (
FILTER (
&& [Value] <> 0
),
[Value]
)``````

If the above one can't help you, please provide more raw data in your table  'Buy' and 'BuyHistory' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors