Set Analysis - Ignore all fields from one table

[berndjaegle]

Hi guys,

looking for a smart solution to ignore selections from one table of my data model.

The data model is with a link table and I want to ignore one table for one KPI.

I know this option: 

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

I don't want to mention all fields explicit. 

Something like

sum({$<[Table.$Fields]=>} Amount)

Any suggestions?

Thank you.

[Somasundaram]

Hi,

we are not able to omit the data from particular Data table . There is no option in Set analysis. 

[berndjaegle]

Thanks for you reply.... also I am not satisfied about it 😐

[marcus_sommer · Accepted solution]

It is possible with something like:

sum({< $(=concat({1< $Table = {'Calendar'}>} $Field, ' ,')) >} Amount)

- Marcus

[sunny_talwar]

And in case you have spaces in your field names, then add square brackets around $Field like this

Sum({<$(=Concat({1<$Table = {'Calendar'}>} '[' & $Field & ']', ' ,'))>} Amount)

[berndjaegle]

Thank you Marcus! Solved my problem!

[sudhirpkuwar]

Hi Sunny,

I want only one particular field to affect the expression result. No other selection should affect.

For ex. When select anything in Status field only than the expression should change, rest any other selection should not affect it.

Is there any way to achieve this?

Thanks in advance.

[marcus_sommer]

You could modify the above approach by removing the table-condition by fetching the fields which will return all fields and from this you replaced your field with nothing, something like:

Sum({<$(=replace(Concat('[' & $Field & ']', ','), '[Status],', ''))>} Amount)

- Marcus

[lalita_sharma]

I have a data model which looks something like below

I am trying to display a straight table on my dashboard which will have columns from table dim_raidlog risks

something like below(only few columns included)

Risk Id	Workstream	Risk Level	Risk RAG	Risk Impact	Risk Likelihood	Risk Response
R059	M&R	Workstream	CLOSED	Medium	Medium	Mitigate
R068	M&R	Workstream	CLOSED	Medium	Medium	Accept
R069	M&R	Workstream	CLOSED	Medium	Medium	Accept
R070	M&R	Workstream	GREEN	Medium	Medium	Accept
R071	M&R	Workstream	CLOSED	Low	Medium	Mitigate
R108	M&R	Workstream	CLOSED	Medium	Medium	Accept

Workstream column has an expression like below:

=IF(GetSelectedCount(Workstream)=0,
AGGR(only({$<[Workstream Id]={$(vCalc_StatusPack_DefaultWorkstreamID)},[KPI name]={3}, [M0 Finish Month]=,[M1 Finish Month]=>}[Workstream]),
[Risk Id],[Workstream],[Risk Level],[Risk RAG],[Risk Impact],[Risk Likelihood],[Risk Response]),
AGGR(only({$<[KPI name]={3},[M0 Finish Month]=,[M1 Finish Month]=>}[Workstream]),
[Risk Id],[Workstream],[Risk Level],[Risk RAG],[Risk Impact],[Risk Likelihood],[Risk Response]))

I don't want the selections on [M0 Finish Month] and [M1 Finish Month] fields to my table on the dashboard.

That is why I have included them in the above expression [M0 Finish Month]=,[M1 Finish Month]=

But still my dashboard table goes blank when I make any selection on these fields. Could someone please help me understand how can I achieve the desired results.

[marcus_sommer]

The association between the involved tables per link-table might not be sufficient to create the wanted view.

Beside this your aggr() has no outer aggregation and is therefore not really an expression else more a calculated dimension. There are scenarios in which it may work especially by an additionally use of TOTAL and/or NODISTINCT but it's not really quite right. The classical aggr() should more look like:

sum({ Conditions } aggr(sum({ Conditions } Field), Dim1, Dim2))

Further are you sure that you need an aggr() at all? And then with so many dimensions? Aggr() should be only used if there are no other ways to calculated the wanted results and if you could accept the performance-impact (they could be very heavy especially by large datasets and link-table approaches and the use of many dimensions).

- Marcus