Need help with DAX measure to show column with Blank value in Table

[Anonymous]

Hello all,

I would need your help for the below requirement. I have tried myself but not meeting complete requirement
I have table with below column which shows open/close response details.

In case of closecategory within 24hrs and after 24Hrs , "closereason" gets translated to defined conutry languages and "emailcloseby" will be tagged to en always and doesn't store value for New/Late closecategory

responseid	closedresponseid	closecategory	emailclosedby	closeReason	languagecode	country
a1	b1	within 24Hrs	abc@gmail.com	testing	en	Belgium
a1	b1	within 24Hrs		testen	nl	Belgium
a1	b1	within 24Hrs		essai	fr	Belgium
a2	b2	after 24Hrs	test@gmail.com	test	en	Portugal
a2	b2	after 24Hrs		teste	pt	Portugal
a3		Late				Italy
a4		Late				Poland
a5		New				Poland
a6		New				France

Report should have slicer of closecategory and languagecode(taken from dimension table) along with table visual which will have all details.
Languagecode slicer should show data in table based on selected value and If nothing is selected show value any among them in case of more than 1 language for one country.

I have written below measure which I should use instead of column closereason in table which filter data correctly based on language but requirement is to show record(with blank value for column closereason)in table when closedcategory is selected all which also includes closecategory=New/Late(langugage code is blank).When closecategory is New/Late then closereason and languagecode will not have data.

Translated_Closereason = SWITCH(SELECTEDVALUE(Sheet1[languagecode]),
"fr",MAXX(FILTER(Sheet1,Sheet1[languagecode]="fr"),[closeReason]),
"nl",MAXX(FILTER(Sheet1,Sheet1[languagecode]="nl"),[closeReason]),
"pt",MAXX(FILTER(Sheet1,Sheet1[languagecode]="pt"),[closeReason]),
"pt",MAXX(FILTER(Sheet1,Sheet1[languagecode]="pt"),[closeReason]),
MAX(Sheet1[closeReason]))

Similarly, I have to write one more DAX for Email which should show always show the value irrespective of languagecode.
Tranlsate_emailcloseby= MAX(emailcloseby) which again having same problem like closereason as row itself gets disappeared

Please see screenshot and refer the  bottom table which doesn't show data for New/Late closecategory.

If there is nothing selected from both slicers then result should be like 

RESULTS :

responseid	closedresponseid	closecategory	emailclosedby	closeReason	country
a1	b1	within 24Hrs	abc@gmail.com	testen	Belgium
a2	b2	after 24Hrs	test@gmail.com	teste	Portugal
a3		Late			Italy
a4		Late			Poland
a5		New			Poland
a6		New			France

Please help with this.
Thanks very much in advance !

 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Sorry, not very clear.

I'm not sure what you expect when filter selection. The following is the result of neither slicer being selected.

Measure = IF(MAX('Table'[emailclosedby])=BLANK()&&MAX('Table'[closecategory])<>"Late"&&MAX('Table'[closecategory])<>"New",1)

Put the measure into the visual level filter, set show items when the value is 1.

Best Regards,

Stephen Tao

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

[Anonymous]

Thanks very much @Anonymous , this solved my problem.
I have also achieved by taking distinctcount of responseid in table which can be hidden.

Thanks again for taking time to look into this

[Anonymous]

@smpa01 @amitchandak @AlexisOlson 
Could you please take a look into this and help me with this.
Sorry for bothering at busy time. 
Your help would be much appreciated! Thanks much in advance