Dynamic Column calculation based on Slicer multiple selection

[Anonymous]

Hi Community,

To explain my request, I take a simple example :

I have a column of data

I use it in a slicer. What I would like, is to generate a measure which will "flag" the selection (with several values) in the slicer (which could done with 1 if selected and 0 if not)

I tried this (inspired by topics mentionned below) :

FLAGESELECTEDROW = IF (
    ISFILTERED ( Feuil1[Data] ) && HASONEVALUE ( Feuil1[Data] );
    LASTNONBLANK(Feuil1[Data];0 )
)

With one selection it is OK :

But nothing with several selection

I read this post (here) which seams to close to my request but I didn't reach to achieve the formula.

My goal is to allow user to perform several selection on slicers in different reports like previously using different columns (country here but also others set of data) to generate measures which could be used to execute specific calculations.

Something like this :

Functionaly speaking, the user "refines" a large selection to focus on short list which is calculated by previous selections

I hope this will be the first step to achieve this !

Thanks for your help.

Regards

Ph

[Thyago_Rezende]

Hi,

It use the parameter table pattern below:

To get the selected value, use the VALUES function in the measure that uses the parameter. Usually you check the selection of one value only. If the selection is of all the parameters, it is like there is no selection at all and, in this case, you can return a default value. If the selection has more than one but not all parameters, you might consider this case as a multiple selection, which in most cases may be an invalid selection. The following code represents the complete pattern that you see applied later in more pattern examples.

1 2 3 4 5 6 7 8 9 10

ParameterSelection := IF (     HASONEVALUE ( Parameter[ParameterValue] ),     "Selection: " & VALUES ( Parameter[ParameterValue] ),     IF (         NOT ( ISFILTERED ( Parameter[ParameterDescription] ) ),         "No Selection",         "Multiple Selection"     ) )

Link from:

http://www.daxpatterns.com/parameter-table/

Regards,

[Anonymous]

Hi @Thyago_Rezende,

Thanks for your answer. I read the article, yet, I don't find which function to use in the case of "Multiple Selection" to fill selected data in column.

In few words, I just want to create a second column with "1" if the data of first column are selected, 0 then.

Thanks for help.

Regards

Ph

[Thyago_Rezende]

Did you try to create roles? Manage rules?

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-rls/

Maybe this help you.

Best regards,

[Anonymous · Accepted solution]

I find a help as a part of the solution in this post by applying the provided formula like this (it is an example) :

After Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow])*(1-[Loss_Select]),
FILTER(ALLSELECTED(CashFlows),CONTAINS(ALLSELECTED(DimDeals),DimDeals[Deal_Index],CashFlows[Deal_Index])),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Contains seems to be help a lot.

The only remained thing is that a slicer can not apply accross reporting

I carry on digging this point.

Regards,

Ph

[Eric_Zhang]

@Anonymous wrote:

I find a help as a part of the solution in this post by applying the provided formula like this (it is an example) :

After Loss Date = 
CALCULATE(SUM(CashFlows[CashFlow])*(1-[Loss_Select]),
FILTER(ALLSELECTED(CashFlows),CONTAINS(ALLSELECTED(DimDeals),DimDeals[Deal_Index],CashFlows[Deal_Index])),
FILTER(ALL(CashFlows),[Deal_Index]=MAX([Deal_Index])&&CashFlows[Date]>[Date_Loss_Select]))

Contains seems to be help a lot.

The only remained thing is that a slicer can not apply accross reporting

I carry on digging this point.

Regards,

Ph

@Anonymous

What do you mean accoss reports? At this moment, the slicer visual in one report page can not interactive with visuals in another page in the same report.

[Anonymous]

Hi @Eric_Zhang,

Yes indeed, there are several recent posts about it. I meant that I have to adapt what seems to be a workaround solution (the previous post) for my use case.

I hope this feature of "slicers accross pages" combined with "dynamic column" (because it is rather difficult to do this with measure event if I am not very skilled yet) will be considered in the roadmap because it seems to be useful for different business cases.

Thank you for your precision.

Regards

Ph