Make measure calculation without taking blanks into account

[Soof1234]

Hi,

I am struggling to creating a measure where it shouldnt take blanks into account. I want to show the percentage of how often a value does occur. 

Below is my measure:

Measure1 =

VAR Teller = COUNTROWS(Sheet1)

VAR Noemer = MAX(Sheet1[Index])

RETURN DIVIDE(teller,Noemer)

Table output =

you see that table output contains blanks which have 53% of the values. Those blank values shouldnt even top up till 100%. All other values combined should be 100% together. Where in my DAX expression should I exclude the blanks?

Thanks in advance!

Kind Regards, 

Soof1234

[v-yueyunzh-msft]

Hi , @Soof1234 

According to your description, you want to "Make measure calculation without taking blanks into account". Right?

 Here are the steps you can refer to :
(1)This is my test data:

'Table' Table:

'Sheet1' Table:

(2)Then we can create two measure to get the Count and the ALL Count:

Count = IF(SELECTEDVALUE('Sheet1'[AMW])=BLANK(),BLANK(), COUNT(Sheet1[AMW]))

ALL count = var _awm = ALL('Table'[AMW])
var _t =FILTER( ALLSELECTED('Sheet1'),'Sheet1'[AMW] in _awm)
return
IF(SELECTEDVALUE('Table'[AMW])=BLANK(),BLANK(),COUNTROWS(_t))

(3)Then we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Soof1234]

Hi @v-yueyunzh-msft ,

I tried with your solution but it still gives me a total count of 286, without blanks it should be 136.

The difference in our columns is that your column ('Sheet1'[AMW]) doesnt have blanks, instead your column ('Sheet1'[Value]) has the blanks. Column Value should be populated every row while AMW should have blanks.

I think this is the only difference in our PBIX file.

Kind Regards,

Soof1234

[v-yueyunzh-msft · Accepted solution]

Hi , @Soof1234 

Thanks for your quick response. According to your description, the [AWM] rows may have BLANK() and the [value] always have the value.And do you have olny one table for this?

If this , you can realize it for two methods:

(1)You can just fliter the [AWM] <> BLANK() in Power Query and not apply this data to Desktop. This method applies that you do not need the data whose [AWM]=BLANK()

(2)For another, you can use this measure:

Count = IF(SELECTEDVALUE('Sheet1'[AMW])=BLANK(),BLANK(), COUNT(Sheet1[AMW]))

ALL count = 
var _t =FILTER( ALLSELECTED('Sheet1'),'Sheet1'[AMW] <> BLANK())
return
IF(SELECTEDVALUE('Sheet1'[AMW])=BLANK(),BLANK(),COUNTROWS(_t))

If you have more than one table and the above methods cannot meeyt your need , can you share the sample data in your table and the relationship between tables to us so that we can help you better.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Soof1234]

Hi @v-yueyunzh-msft , 

Thanks for your quick response:) I used your second method as solution because I cant filter away the blanks from that column in power query because other columns on the same row have data that I need for other purposes.

Kind Regards, 

Soof1234

[djurecicK2]

Hi @Soof1234 ,

 Instead of VAR Teller = COUNTROWS(Sheet1), you can use 

VAR Teller = COUNT(Sheet1[verwijzer naar AMW])

COUNT automatically excludes blank values

https://learn.microsoft.com/en-us/dax/count-function-dax

Please accept as solution if this answers the question- thanks!