topic Re: Bypassing field in calculated dimension in App Development

Bypassing field in calculated dimension

aatish12345 — Fri, 02 Jun 2023 04:11:17 GMT

Hi All,

I have below calculated dimension for "SalesDate" in straight table:

Aggr(Only( [SalesDate]={"=[SalesDate]<=ReportingDate"}>}[SalesDate]),
[SalesDate])...In this calculated dimension I want to bypass Region field, I have done it as shown below

Aggr({<[Region]=>} Only( [SalesDate]={"=[SalesDate]<=ReportingDate"},[Region]=>}[SalesDate]),
[SalesDate])

The expression syntax is OK but in the Region filter is still getting applied on the data.

I want to know is there any other way of bypassing the region field in order to avoid its selection on the data.

Aatish

Re: Bypassing field in calculated dimension

BrunPierre — Fri, 02 Jun 2023 13:30:20 GMT

Hi, How about an expression just like this and then using [SalesDate] as the dimension?

AggregationFunction ({$<[Region]=, [SalesDate]={"<=$(=ReportingDate)"}>} Measure)

Re: Bypassing field in calculated dimension

aatish12345 — Fri, 02 Jun 2023 08:05:34 GMT

Using only on the top of my expression wont allow the table to populat data as it is a date field used as dimension

Re: Bypassing field in calculated dimension

aatish12345 — Fri, 02 Jun 2023 08:10:33 GMT

this is the error , since it is a calculated dimn so aggr function like only() it is not allowing

Re: Bypassing field in calculated dimension

BrunPierre — Fri, 02 Jun 2023 13:30:05 GMT

Hi, How about an expression just like this and then using [SalesDate] as the dimension?

AggregationFunction ({$<[Region]=, [SalesDate]={"<=$(=ReportingDate)"}>} Measure)

Re: Bypassing field in calculated dimension

aatish12345 — Fri, 02 Jun 2023 09:34:11 GMT

I will get back to you

Re: Bypassing field in calculated dimension

aatish12345 — Mon, 05 Jun 2023 07:10:19 GMT

Hi,

You mean adding another expression with aggregation function and then using SalesDate as a dimension?

Aatish

Re: Bypassing field in calculated dimension

BrunPierre — Mon, 05 Jun 2023 07:25:35 GMT

Something like.

Sum({$<[Region]=, [SalesDate]={"<=$(=ReportingDate)"}>} Sales)

Re: Bypassing field in calculated dimension

aatish12345 — Mon, 05 Jun 2023 09:22:50 GMT

I cant use sum function since the field is Date field and not any numeric field.

Re: Bypassing field in calculated dimension

BrunPierre — Mon, 05 Jun 2023 09:41:20 GMT

If you want to show the dates as measure, then maybe something like this.

Only({$<[Region]=, [SalesDate]={"<=$(=ReportingDate)"}>} [Date field])

Re: Bypassing field in calculated dimension

aatish12345 — Mon, 05 Jun 2023 10:58:49 GMT

Showing date field as measure is also not acceptable by users, as they are selecting date from date field in straight table., and if i use date field as measure then selection wont be possible by users

Re: Bypassing field in calculated dimension

BrunPierre — Mon, 05 Jun 2023 13:10:53 GMT

Share a snippet of the output table.

Re: Bypassing field in calculated dimension

aatish12345 — Mon, 05 Jun 2023 15:15:40 GMT

here I used only function on SalesDate as a measure, but qs wont allow a selection on measure data, but my users want to select SalesDate for deep analysis of data., hence i was using it as calculated dimension

Re: Bypassing field in calculated dimension

BrunPierre — Mon, 05 Jun 2023 16:32:08 GMT

Only({1<[SalesDate]={"<=$(=ReportingDate)"}>} [SalesDate])

Aggr(Only({1<[SalesDate]={"<=$(=ReportingDate)"}>} [SalesDate]), [SalesDate])

Re: Bypassing field in calculated dimension

aatish12345 — Thu, 08 Jun 2023 14:01:13 GMT

Hi Brun,

the solution : Aggr(Only({1<[SalesDate]={"<=$(=ReportingDate)"}>} [SalesDate]), [SalesDate])

is working perfectly when i bypassed region field as below:

Aggr({<Region=>}Only({1<[SalesDate]={"<=$(=ReportingDate)"},Region=>} [SalesDate]), [SalesDate])

But it is strange the same solution was not working when I used [SalesDate]={"=[SalesDate]<=ReportingDate"}>}

in the set analysis., but once I replaced it with your code i.e., [SalesDate]={"<=$(=ReportingDate)"}>} , it is working perfectly., Thanks a lot for this help. Will really appreciate your efforts.

Happy learning!

Aatish