2 aggregate values from same set of columns that have to be filtered differently for both values

[KMZ_ESS]

Dear all,

I have 3 columns that contain different infos of accounts. I'm trying to create a chart that contains 2 values:

1. the count of accounts that have blanks in all 3 columns

2. the count of accounts that have at least one entry in any of the 3 columns

Restrictions: I have to work with our PowerBI dataset, Excel is not an option and for reasons I don't understand our IT doesn't allow users to add/create columns when using our dataset.

My idea was that I put the 3 columns in the legend field, filter all 3 columns by blanks and put "count of account number" in the value field. Then I have to subtract that value from the total number of accounts. I assume I can create a measure for that and then enter the measure as the second value in the value field. But I don't know how to create the measure and don't know whether the filters of the 3 columns are also applied to the measure or if the measure is "standalone".

I hope I was able to explain the issue. I appreciate any guidance and kindly ask you to keep in mind I'm still a beginner. Many thanks! 🙂

Best regards

Kaveh

P.S.: I'd appreciate it if someone would tell me how they would've described the issue. I have a hard time finding the right words. Thanks in any case! 🙂

[Arul · Accepted solution]

@KMZ_ESS ,

try this measures,

Blank in all three columns = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Column1] == BLANK() &&
    'Table'[Column2] == BLANK() &&
    'Table'[Column3] == BLANK())

No blank in all three columns = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Column1] <> BLANK() ||
    'Table'[Column2] <> BLANK() ||
    'Table'[Column3] <> BLANK())

Thanks,

Arul

[KMZ_ESS]

@Arul 

This is what I'd like to do: 

Column1	Column2	Column3
1
	1	1
1		1
		1
1	1
	1
1	1	1

[Arul · Accepted solution]

@KMZ_ESS ,

try this measures,

Blank in all three columns = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Column1] == BLANK() &&
    'Table'[Column2] == BLANK() &&
    'Table'[Column3] == BLANK())

No blank in all three columns = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Column1] <> BLANK() ||
    'Table'[Column2] <> BLANK() ||
    'Table'[Column3] <> BLANK())

Thanks,

Arul

[KMZ_ESS]

@Arul Many thanks for the quick response!

I was able to reproduce the result of the first measure in our CRM. 🙂

So far I cannot reproduce the result of the second measure in our CRM or Excel. What I did:

- export all rows that contain data in Column1, 2 and 3 respectively.

- put the results of the 3 exports in one table

- remove the duplicates

The result of the measure is 32k. The result of the above actions is 23k. I checked all filters, they are the same in PBI and CRM. I'm not sure what the cause could be for the different results. 😞 Now trying to come up with more ways to check.

[Arul · Accepted solution]

@KMZ_ESS ,

Can you check the OR (or) AND operators are working fine or not in second measure?

Thanks,

Arul

[KMZ_ESS]

I actually have to backtrack on my previous statement. If I check the numbers in CRM I get 88k blanks and 23k non-blanks. PBI also states the total as 111k but with a split of 79k vs. 32k (when using the measure). I also come up with 79k if I use only filters instead of the measure. So there are 2 ways to come up with 79k blanks in PBI, but there are also 2 ways to come up with the number 23k for non-blanks in CRM. So this doesn't seem to be an indicator for validity of the numbers.

I've checked the filters in CRM and PBI over and over again. I don't see anything different. I'll have to speak to the dataset owner on Monday to see if there is something different between the use of filters in CRM and PBI that I'm not aware of.

[KMZ_ESS]

@Arul It was an issue in the dataset. Your measure is correct! 🙂 Thanks for the quick help!

[Arul]

@KMZ_ESS ,

Would be better if you posted some sample data.

Thanks,

Arul