Bar chart - Filter on this Visuals -> basic filtering where value = Measure

[jayvataliya]

Hi All,

I have a bar chart representing Company and Amount.

• A, B, C,... is the company name

I created a measure that gives me the top 1 fail reason by amount.

1. Eg. Wrong Input 

I want to filter the chart where Reason  = Measure(Top 1 fail reason)

I tried many ways but no luck, your help will be appreciated.

Thanks

[v-yingjl]

Hi @jayvataliya ,

Sorry for replying late. Based on your description, you actually create a measure to show the top1 fail reason name:

Top1 fail =
VAR tab =
    SUMMARIZE (
        ALL ( 'Source table' ),
        'Source table'[FailReason],
        "Sum", SUM ( 'Source table'[Amount] )
    )
VAR tb =
    ADDCOLUMNS ( tab, "rank", RANKX ( tab, [Sum],, DESC, DENSE ) )
RETURN
    MAXX ( FILTER ( tb, [rank] = 1 ), [FailReason] )

But you can not put the measure in the 'text' field in the filter when you are using Advanced filter.

Instead, you can use the TopN filter directly to ger the top 1 fail reason by the sum of Amount for each reason.

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jayvataliya]

Thank You @v-yingjl  for your reply. It definitely helped to get the rank of fail reason.

However, I am trying to display the data of each reason in a separate chart visual. 

• Eg.
  • Chart 1 = Wrong Input
  • Chart 2 = AAA
  • Chart 3 = BBB
•  

At this point, the chart shows all of the reason values.

[v-yingjl]

Hi @jayvataliya ,

As I previous post, just adjust the TopN filter as Top1,2,3 for Amount for each bar chart so that you can get the expected output.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jayvataliya]

hmm, it seems like TopN doesn't work that way.

In the below screenshot, the 2nd chart(2.AAA) shows both the reason instead of only the 2nd fail reason. it is trying to find TOP 2 reasons by the amount.

[v-yingjl · Accepted solution]

Hi @jayvataliya

Keep the previous measure and create a visual control measure for each visual and apply it in the visual filter:

visual control_1 = 
IF ( SELECTEDVALUE ( 'Source table'[FailReason] ) = [Top1 fail], 1 )

Please refer the below sample file, hopes it could help.

[jayvataliya]

Great. This is working.

Thank you @v-yingjl really appreciate your help here.  

Anybody who is reading this post, give it a thumbs up 👍

[lbendlin]

"I created a measure that gives me the top 1 fail reason by amount."

How?  Did you use RANKX ?

[jayvataliya]

Yes, you are right. I followed the below steps to get the top 1 fail reason.

• I create a separate table using SUMMARIZE() from the main dataset (Columns: Year, Month, FailReason, Amount)
• Created a measure to Sum the amount 
  • AmountSum = SUM('Table'[Amount])
• Created a measure that gives the rank of fail reasons by the sum of the amount 
  • RK = RANKX(ALL('Table'[FailReason]),[AmountSum],, DESC, Dense)
• And, created the last measure that gives me the top 1 fail reason by rank
  • TopFailReason = TOPN(1,VALUES('Table'[FailReason]),CALCULATE([RK] = 2),DESC)

NOTE: here I am getting top 1 fail reason where Rank = 2(2nd fail reason)