How to add filter at AMT_REVENUE_C ?

[Paulyeo11]

Hi All

I need to add filter at AMT_REVENUE_C 

So that it only plot 2 company sales line.

( I aware that i can using Bookmark to filter these 2 company , But due to some reasons , i need to hard code the )

My PBI file :-

https://www.dropbox.com/s/j7g53rpz32s2btw/COMPETITOR_V006.pbix?dl=0

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

Click on Apply filter 

Before

After

Phil

[Paulyeo11]

Hi Phil

Thank you very much. it work now

[PhilipTreacy]

Hi @Paulyeo11 

Here's your PBIX with the filter working.

With the visual selected, I dragged the Measure to the Filters area then set it to Show items when the value is 1.

I then added a few more companies to the list in the Measure so the visual is now showing this

Just add more companies to the list in the Measure to see them displayed.

Regards

Phil

[Paulyeo11]

Hi Phil

Thank you very much.

I try to duplicate what and how to make it work , i still can not get it right .

[v-zhenbw-msft]

Hi @Paulyeo11 ,

Do you want to show ten COMPETITOR without slicer?

If yes, you can create a measure, then put it in Filter on this visual and configure it as 1.

Measure = 
IF(
    MAX(COMPETITOR[COMPETITOR]) in {"OMRON","PHOENIX"},1,0)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Paulyeo11]

Hi Zhen

I have download the PBI file and can see it work fine.

But i cannot duplicate in my actual PBI file  , the issue i face is i am unable to move the measure into the filter box.

Paul

[v-zhenbw-msft]

Hi @Paulyeo11 ,

You need to select the line chart and add the measure in Filter on this visual.

Or you can put it in Tooltips, and configure it in Filter pane.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Paulyeo11]

Hi Zhen

I manage to drag the Measure to filter box , But I am still unable to make it work. 

Below is my PBI file :-

https://www.dropbox.com/s/z166bqdo1f5os9z/COMPETITOR_V006-Dual-Axis-Paulyeo11%20NOT%20WORKING%20ON%2...

Paul

[PhilipTreacy]

HI @Paulyeo11 

I thught you only needed to plot data for 2 companies, not many expressions?

The original measure didn'twork, I think perhaps Fowmy thought that AMT REVENUE_C was another measure but the code provided did notwork and requires the column AMT REVENUW_C to br aggregated in some way hence using SUM and yes this will produce a single line.  Which is why I created 2 separate measures for the 2 companies you said you wanted.

Regards

Phil

[Paulyeo11]

Hi Phil

Thank you for your sharing.

May i know i need to create 10 seperate expression , if i need to plot 10 company line chart ? there is no way that i can create just one expression and insert those 10 company name in the expression ?

Paul

[PhilipTreacy]

Hi @Paulyeo11 

There are a few typos with that measure.  The word Table is reserved so if you have a table callled Table is has to be in single quotes when you refer to it in measures.  There's also an extra = in there that shouldn't be.  You also need to specify what aggregation to use on the AMT REVENUE_C column so you end up with this

AMT = CALCULATE ( SUM([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="OMRON" || 'TABLE'[COMPETITOR]="PRECICON"))

But I'm not sure that is what you are actually after?  I'm not clear but you could create 2 measures

AMT Omron = CALCULATE ( MAX([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="OMRON"))

AMT Precicon = CALCULATE ( MAX([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="PRECICON"))

and plot these to get 

But maybe I'm misunderstanding what you want?

Please check this PBIX file for the above DAX and visual

Regards

Phil

[Paulyeo11]

Hi Phil

Thank you very much for sharing.

Yes that is one way , only issue is i need to create many expression.

it is possible to combine below 2 expression into 1 ?

AMT Omron = CALCULATE ( MAX([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="OMRON"))

AMT Omron = CALCULATE ( MAX([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="PRECICON"))

I just notice you have create below expression , but it only plot one line. what i need is it should plot 2 line.

AMT = CALCULATE ( SUM([AMT REVENUE_C]), FILTER(ALLSELECTED('TABLE'[COMPETITOR]) , 'TABLE'[COMPETITOR]="OMRON" || 'TABLE'[COMPETITOR]="PRECICON"))

Paul Yeo

[PhilipTreacy]

No worries @Paulyeo11   Your initial image showed the slicer with 2 companies highlighted and asked how to select data for those 2 specific companies.

[Fowmy]

@Paulyeo11 

Try this measure:

CALCULATE ( [AMT_REVENUE_C], FILTER( ALLSELECTED(TABLE[COMPETITOR]) = TABLE[COMPETITOR]="PRECICON" || TABLE[COMPETITOR]="OMRON"))

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn 

[Paulyeo11]

Hi Fowmy

Thank you for your help.

I get error msg :-

https://www.dropbox.com/s/j7g53rpz32s2btw/COMPETITOR_V006.pbix?dl=0

Above link is my PBI file

Paul

[PhilipTreacy]

Hi @Paulyeo11 

Use CTRL+Mouse Click to multi-select the filters

Or turn off Multi Select with CTRL in the Format area of the Slicer to allow you to just click what Competitors you want displayed

Phil

[Paulyeo11]

Hi Phil

Thank you very much for sharing the tips and trick. But for my actual application , i have more then 50 company , so i need hard code . which is what Fowmy advise me.

Paul