topic Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib in DAX Commands and Tips

Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 07:34:58 GMT

Hello,

how to show null when attribute is placed as single in table and to show values when placed in combination of other attributes in table.

for example: we have 3 columns in table stating product category, company and brand

so when product category or company is placed in matrix table it should be shown as null, and when placed in combination with brand(drilldown way) it should display values/data.

trying to create DAX using isfiltred function but not helping.

Thanks in advance.

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

ValtteriN — Wed, 08 Dec 2021 08:04:14 GMT

Hi,

I am not sure if I understood your issues, but from what I understood you want to show nulls on "1st level" of a matrix and when you drill down you would display values?

Like you suggested this can be achieved with ISFILTERED. Here is a fomula example:

Matrix with Blanks on lvl 1 = IF(

ISFILTERED('Matrix example'[Lvl 1])&&ISFILTERED('Matrix example'[Lvl 2])=FALSE(),"", //This checks if the current row is level 1 if so -> returns blank else returns the calculation below

SUM('Matrix example'[Value])) //Sum can be replaced with measure

End result:

The key here is to use multiple ISFILTERED functions with true and false conditions.

Hopefully this helps to solve your issue and if it does consider accepting this as a solution!

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 08:27:11 GMT

this is what i am expecting..

hope i am clear.

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

ValtteriN — Wed, 08 Dec 2021 08:43:28 GMT

Okay, I think I got it now. Unfortunately, according to my understanding a single table and level 1 of a matrix operate the same way so it is not possible to have one showing nulls and the other showing values with the same formula. Maybe we should try a different approach? What is reason behind this measure so why do you need it?

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 08:51:52 GMT

values for company, category are not correct at higher level i mean when selected as single attribute in table, but when i drill down with brand then values are correct at that level, so aggregated values are not correct, which should be avioded , and we are using this power bI dataset as source to different dasbaords unlike sql server or azure etc.., so to aviod developers use wrong info we need to hide data at upper level.

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 08:53:13 GMT

type mistake, power BI dataset used as datasource like sql,azure etc..

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

ValtteriN — Wed, 08 Dec 2021 09:21:20 GMT

In this case shouldn't a simple,

IF(ISFILTERED('Matrix example'[Brand]),SUM('Matrix example'[Value]),"")
Do the trick?
If the information is correct when filtered via brand then this condition should ensure the validity of data.

Example of how it looks:

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 10:55:46 GMT

can you share pbix with me, i tired similar method but its not working for me

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Wed, 08 Dec 2021 12:56:51 GMT

how about if i have to drilldown by company and brand?

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

ValtteriN — Wed, 08 Dec 2021 13:48:34 GMT

If the data is correct only with those two drilldowns in place then,

IF(and(ISFILTERED('Matrix example'[Brand]),ISFILTERED('Matrix example'[Company])),SUM('Matrix example'[Value]),"")

Should do the trick.

Then to get this to work in a matrix you can use Brand as a column and Company as a row:

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Thu, 09 Dec 2021 04:17:25 GMT

Thanks ValtteriN i tired this, but unfortunately my client wants it to have both in columns with filtering in both directions..

thank you so much..

thanks,

Geetha.

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

V-lianl-msft — Mon, 13 Dec 2021 06:35:08 GMT

Hi Anonymous ,

Is this the result you expect?

Try to create a measure like below:

Measure = IF(ISINSCOPE('Table'[Brand]),SUM('Table'[value]))

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

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

Anonymous — Mon, 13 Dec 2021 07:17:21 GMT

Thank you, but this not what i am expecting, measure should not be blank for prod_category/company.

if prod-category/company slected individually then it show blank, in hierarchial struture it should populate the values.

Re: Show Nulls when attribute is selected and show values when selected in combination with other attrib

V-lianl-msft — Mon, 20 Dec 2021 06:29:28 GMT

Hi Anonymous ,

Try this measure:

Measure 2 = IF(ISINSCOPE('Table'[product category]),IF(ISINSCOPE('Table'[company ]),SUM('Table'[Value]),BLANK()),BLANK())

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