Show all rows in comment column based on filter

[sadiahsapiee]

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

[stevedep · Accepted solution]

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:

 

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

Please note that it requires this datamodel.

Power BI file is here.

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

Kind regards,

Steve. 

[sadiahsapiee]

Hi Steve,

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

[Anonymous]

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

[sadiahsapiee]

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.