topic Re: mulitple counts not wanted, just count once in DAX Commands and Tips

mulitple counts not wanted, just count once

ASS — Mon, 20 Nov 2023 08:13:57 GMT

Hi,

I need some help with a DAX meassure to calculate the right values.

The date table is connected to the reporting date. The fact table is a full load dump by the report date. That means the fact are repeated month by month. To count the distinct value of the category is fine, but the category has a related amount. e.g category 1X!X! has 15 pcs. The amount of 15 pcs are multipled by the number of reporting date.

The category has a cancelation date, but i don´t want to multiply each reporting month. I just want to count the latest amount (for the cancalation date, which is connected to the date table. Or the last date whre the cancalation date is recorded.

Any suggestion?

Re: mulitple counts not wanted, just count once

123abc — Mon, 20 Nov 2023 08:58:08 GMT

It looks like you're working with DAX (Data Analysis Expressions) in Power BI or a similar tool and you want to create a measure that calculates the distinct count of a category, taking into account the cancellation date and only considering the latest amount for that category.

Assuming you have a fact table with columns like "Category," "Amount," and "CancellationDate," and a date table connected to the reporting date, here's a possible approach using DAX:

DistinctCountLatestAmount =
CALCULATE(
MAXX(
VALUES('YourTable'[Category]),
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[CancellationDate] = MAX('YourDateTable'[Date])
)
)
)
)

This measure uses the CALCULATE function to create a context transition and then applies the MAXX function to iterate over the distinct values of the 'Category' column. For each category, it calculates the count of rows where the cancellation date matches the maximum date in the date table. The MAXX function then returns the maximum value of this count for all categories.

Make sure to replace 'YourTable' and 'YourDateTable' with the actual names of your tables. Adjust the column and table names in the DAX code to match your data model.

This measure should give you the distinct count of categories considering only the latest amount for each category based on the cancellation date.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: mulitple counts not wanted, just count once

ASS — Mon, 20 Nov 2023 09:24:16 GMT

Hi, thanks for reply, but the distinct count of the category is not the problem. The summarization over the repeated reporting dates of the amount needs to be solved.

Re: mulitple counts not wanted, just count once

123abc — Mon, 20 Nov 2023 09:39:04 GMT

I see, I misunderstood your initial question. If you want to avoid the multiplication of the amount for each reporting date and only consider the latest cancellation date, you can modify the measure as follows:

LatestAmount =
CALCULATE(
SUMX(
VALUES(FactTable[Category]),
VAR LatestCancellationDate = CALCULATE(
MAX(FactTable[CancellationDate]),
ALLEXCEPT(FactTable, FactTable[Category])
)
RETURN
CALCULATE(
SUMX(
FILTER(FactTable, FactTable[CancellationDate] = LatestCancellationDate),
FactTable[Amount]
)
)
)
)

This modified measure introduces a variable (LatestCancellationDate) to calculate the latest cancellation date for each category. The ALLEXCEPT function is used to remove the context of all columns in the FactTable except the Category. Then, it calculates the sum of amounts for the rows where the cancellation date matches the latest cancellation date.

The outer SUMX then iterates through the distinct categories, and for each category, it calculates the sum of amounts based on the latest cancellation date.

This should give you the sum of amounts for each category, considering only the latest cancellation date, and avoiding multiplication for each reporting date.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: mulitple counts not wanted, just count once

123abc — Wed, 22 Nov 2023 07:25:00 GMT

I see, my apologies for the misunderstanding. If you want to avoid counting the amount multiple times for repeated reporting dates, you can modify the DAX measure as follows:

TotalAmountLatestCancellation =
CALCULATE(
SUMX(
VALUES('FactTable'[Category]),
CALCULATE(
MAXX(
FILTER(
'FactTable',
'FactTable'[CancellationDate] = MAX('DateTable'[Date])
),
'FactTable'[Amount]
)
)
)
)

Here, I replaced LASTNONBLANK with MAXX. This change ensures that only the maximum amount for each category on the latest cancellation date is considered. This should prevent the multiplication of amounts for repeated reporting dates.

Please test this measure in your Power BI environment and adjust it according to your specific data model if needed.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.