topic Re: Get count of rows in running total measure in DAX Commands and Tips

Get count of rows in running total measure

LLJ1221 — Fri, 10 Nov 2023 16:59:52 GMT

Hello,

I am a beginner in Power BI and I have a problem calculating the number of occurrences of measure.

I have 2 problems :

1) I want to know the number of distinct item items I have in measure "Difference Running Total A & B"

Example, in my table , i have 2 article in september 2023. Which formula dax i can do ?

2) I want to know the number of items difference from zero in measure "Difference Running Total A & B"

in my example, i have 2 article with difference running total A&B <>0

My data it's Year, Month, Article and Storage.
I calculate Running total A, Running total B and Difference Running Total A&B in Power bi with measures

Running total A = CALCULATE(

SUM( 'Table'[QuantiteA] ),

FILTER(

ALLSELECTED( 'CALENDAR' ),

'CALENDAR'[Date]

<= MAX('CALENDAR'[Date] )

))

Running total B = CALCULATE(

SUM( 'Table'[Quantite B] ),

FILTER(

ALLSELECTED( 'CALENDAR' ),

'CALENDAR'[Date]

<= MAX('CALENDAR'[Date] )

))

Difference Running Total A&B = [Running total A] - [Running total B]

My data model :

To better understand my problem, I created a pbix file which can be downloaded via One Drive : https://1drv.ms/u/s!AjUN6-w6YnuGz1ng157xR5Ha7h_x

Re: Get count of rows in running total measure

some_bih — Sun, 12 Nov 2023 21:12:18 GMT

Hi LLJ1221 your measure use reference to another measure which use ALLSELECTED. This is not best practice as result is hard to interpret; hard to spot unlogical output in results. ALLSELECTED is best used for visuals.

Rewrite your measure/s.

Re: Get count of rows in running total measure

LLJ1221 — Sun, 12 Nov 2023 23:09:12 GMT

Hi some_bih

Thank you for your feedback and for this explanation.

I'm a beginner in Power BI and I spent a lot of time trying to write these measures with "ALLSELECTED".

How could I rewrite these measurements please?

Thanks

Re: Get count of rows in running total measure

some_bih — Mon, 13 Nov 2023 06:50:42 GMT

Hi LLJ1221 something went wrong so I could not open your file.

Try two measures below

Test A=

VAR __max_date = MAX ( 'Calendar'[Date] )

RETURN

CALCULATE (

SUM( 'Table'[QuantiteA] ),

'Calendar'[Date] <= __max_date

ALL ( 'Calendar' )

)

Test B=

VAR __max_date = MAX ( 'Calendar'[Date] )

RETURN

CALCULATE (

SUM( 'Table'[Quantite B] )

'Calendar'[Date] <= __max_date

ALL ( 'Calendar' )

)

Re: Get count of rows in running total measure

LLJ1221 — Mon, 13 Nov 2023 12:55:55 GMT

Thanks you, I modifiy my dax formula and it's ok.

But i try to count number of article i have and number of article with difference between cumul A & B is zero and the résult is bad.

DAX Formula for count number of article (items) :

Nb item = CALCULATE(DISTINCTCOUNT('Table'[ITEM]), FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date].[Date] <= MAX('Calendar'[Date].[Date]) && (NOT ISBLANK([Diff_cumulAB]))))Dax

formula for count number of article with Diff_cumulAB =0 Nb item equal 0 =

CALCULATE(DISTINCTCOUNT('Table'[ITEM]), FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date].[Date] <= MAX('Calendar'[Date].[Date]) && [Diff_cumulAB]=0))

Normally I should have 5 items and 2 items whose last digit "Diff_cumulAB" is equal to 0

Re: Get count of rows in running total measure

some_bih — Mon, 13 Nov 2023 13:44:30 GMT

--formulas (both of them) after FILTER - filters Calendar table and [Diff_cumulAB]
--[Diff_cumulAB] is not part of Calendar table? If yes then rewrite formula

Re: Get count of rows in running total measure

LLJ1221 — Mon, 13 Nov 2023 14:13:56 GMT

Why I have to rewrite the formula given that it works, I don't quite understand.

thanks anyway

Re: Get count of rows in running total measure

some_bih — Mon, 13 Nov 2023 14:55:56 GMT

Hi LLJ1221 CALCULATE formula usually filter argument, still the filters could be inserted simply using commas

filter1, filter1....

In your case I do not understand what is [Diff_cumulAB] but you use it in part formulas (both of them) after FILTER - filters Calendar table

Re: Get count of rows in running total measure

LLJ1221 — Mon, 13 Nov 2023 15:51:35 GMT

I find the solution.

The solution is in this pbix here : DATA_COUNT_ITEMS.pbix