topic Re: / 30 (days) in DAX Commands and Tips

/ 30 (days)

Anonymous — Thu, 11 Aug 2022 10:44:11 GMT

Hi All,

This is my current formula (see the top formula below), but it does not work correctly because some days there is no activity so I need to tell PowerBI to include the empty cells as 0.

SDR ID average per CP ID average per Day =

AVERAGEX(

VALUES('Calendar'[Date]),

CALCULATE([SDR ID average per CP ID])

)

I have tried:

SDR ID average per CP ID average per Day (0) =

AVERAGEX(

VALUES( 'Calendar'[Date] ),

[SDR ID average per CP ID] + 0

)

and:

SDR ID average per CP ID avgerage per Day(coalesce) =

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

but the results are still not working.
I would like to try to rewrite the formula but using Month in the callendar and divided by 30)
That would be close enough, just not sure how to do this.

I would appreciate any assistance on this.
Thank you greatly in advance.

Best,

Re: / 30 (days)

Anonymous — Thu, 11 Aug 2022 13:44:21 GMT

Hi,

WIth regards to myt message earlier. I've tested it and in theory the "+ 0" and the "coalesce" formula work.
The issue is that I get the average by the Connector, not by the Charger Type.
I use my formula:

SDR ID average per CP ID avgerage per Day(coalesce) =

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

It works well with my Connectors but it doesn't average the 3 Connectors onto one 'Charger Type'
See the tables:

The charger type should give me 0.59, instead it sums up the three connectors (1.77)
What can I do?
Please assist, and thank you in advance.
Best,

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 09:45:55 GMT

Anonymous hey, can you share a sample file maybe?

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 10:29:40 GMT

Hi SpartaBI,

I think the previous issue is not that relevant now.
Could you please help me with this function instead?

SDR ID average per CP ID avgerage per Day(coalesce) =

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

This function calculates the 0 even if the charger type wasn't in the ground then and it calcultes data in Q4 this year.
How to tell the function to take only data when the charger is actualy active please?
Thank you,

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 10:53:07 GMT

Anonymous wrap it with calculate like this:

CALCULATE(

[Your function],

TableName[Charger] = 'Active'

)

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 11:21:07 GMT

I'm not that profficient in Dax:

SDR ID average per CP ID avgerage by Date2 =

CALCULATE(

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 ),

[FACT TABLE(Charger Type]) = 'Active'

)

Please rearrange for me. This is obviously wrong. Thank you so much

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 11:36:46 GMT

Anonymousthe code is correct, just remove the [ before the FACT.

If you don't get the right result then the question does the original measure works fine for all charger types?

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 11:43:09 GMT

Average Durations =

CALCULATE(

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 ),

FACT TABLE[Charger Type] = 'Active',

)

I am getting an error: "Too many arguments for AVERAGEX, max is 2

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 11:52:01 GMT

Add another ) after 0 ) so you will have there 0 )),

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 12:01:00 GMT

Average Durations =

CALCULATE (

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )),

'FACT TABLE'[Charger Type] = 'Active'

That does look good, the problem is that I am getting an error: 'Cannot find table 'Active'.
I do not have a table with Active/Non Active column.
Is there a different way of doing this. My line graph is showing "0" according to the calendar before the item started operating and runs into the future months (I guess as per the calendar).

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 12:10:56 GMT

Well, that is because you replace blank with 0 in your original measure.

So forget what we did, it's not related to your issue.

Why did you replace the blank with 0 inside the coalesce? You have blanks inside the range?

Anyway, if you share the file and what you need I could solve this quickly. Will need to continue later, going away from PC, so will ping here later tonight. Until then please try to share the file or a sample of it.

P.S. appreciate your kudos

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 12:14:28 GMT

Thank you very much, I will share that with you later.
Best wishes,
J

Re: / 30 (days)

Anonymous — Fri, 12 Aug 2022 15:18:08 GMT

Hi SpartaBI,
I'm very gratefull for your assistance.

This function I currently use:

Average Sessions =

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

This is the formula that sort of works. It returns the results correctly, averaging the usage data including the days when the unit wasn’t in use, but the issue is that My calendar starts in 2020 so the formula calculates the Averages from 2020 not when my Connector was first used in 2021 Q3, and runs to Q4 2022, as per my calendar.
I just need the function to calculate all the O’s between the 1st use until last usage, e.g. today.

With this function I am calculating the SDR ID(unique number) Average per CP ID (unit number) Average, per day;
Some days the items are not in use, so that’s why I thought I need to add the coalesce calculation.

I am filtering the above function by the column (FACT TABLE[Connector]).

Please let me know if there's anything else you need to figure this out.

Thanks again,

Re: / 30 (days)

SpartaBI — Fri, 12 Aug 2022 17:13:12 GMT

Hey, won't be able today. Tomorrow ping me here and we'll do a quick zoom to look together