cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sadiahsapiee
Helper I
Helper I

Show all rows in comment column based on filter

My Table1 like below:

 

Type              Market         Country      Month           Comment                                 Level

Business       Europe          Italy            Apr20        Project Completed                      Global

Business       Europe          Italy            Apr20       Continue focus on automation   Market

Business       Europe          Denmark    Apr20       Testing ongoing                          Market

 

If Market slicer select Europe, I want it to display row 2 & row 3.

if Market slicer select All, I want it to display row 1

 I try apply below using a few exp like FirstNonBlank but still not work.  
View= If( HASONEFILTER(Table1[Market];

             Calculate(<exp>(Table1[Comment]); Filter(Table1; Table1[Level]="Market");

            Calculate(<exp>>(Table1[Comment]); Filter(Table1; Table1[Level]="Global")

            )

what <exp> should i use? I tried FirstNonBlank but only one row is displayed. Is there any formula should I use? 

Thank in advance

1 ACCEPTED SOLUTION
stevedep
Memorable Member
Memorable Member

Hi,

 

This should be what you are looking for:

SelectedValue = 
var v_filterenabled = ( ISFILTERED(Market_Country[Country]) || ISFILTERED(Market_Country[Market]))
var v_filtervalue = IF(v_filterenabled;"Market";"Global") 
return
CALCULATE(COUNTROWS('Table');FILTER('Table';'Table'[Level]=v_filtervalue))

 

When no filter is applied is shows the global record:

nofilter.png 

When a filter is applied is shows the "Market" record:

withfilter.png

Please note that it requires this datamodel.

datamodel.png

 

Power BI file is here.

 

Please mark as solution if this is what you are looking for. Thanks!

 

Kind regards,

 

Steve. 

View solution in original post

4 REPLIES 4
stevedep
Memorable Member
Memorable Member

Hi,

 

This should be what you are looking for:

SelectedValue = 
var v_filterenabled = ( ISFILTERED(Market_Country[Country]) || ISFILTERED(Market_Country[Market]))
var v_filtervalue = IF(v_filterenabled;"Market";"Global") 
return
CALCULATE(COUNTROWS('Table');FILTER('Table';'Table'[Level]=v_filtervalue))

 

When no filter is applied is shows the global record:

nofilter.png 

When a filter is applied is shows the "Market" record:

withfilter.png

Please note that it requires this datamodel.

datamodel.png

 

Power BI file is here.

 

Please mark as solution if this is what you are looking for. Thanks!

 

Kind regards,

 

Steve. 

Hi Steve,

 

Thank you very much for your solution. Really appreciate it. 

Anonymous
Not applicable

Hi,

Can you please clarify what end result you are trying to obtain? If you added the [Market] column to a slicer and selected Europe, it would return all 3 columns. Are you trying to slice by [Level] instead?

 

Thanks,

Kim

Hi Kim,

 

Thanks for the respose.

My slicer is only [Market] and [Country]. If both slicer NOT select anything (All), then only row that has 'Global' value in column Level is displayed. I don't want create [Level] as slicer.

 

Thanks in advance. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors