(Direct Query) Count Values in column B depending on the value in column A

[Anonymous]

Hi,

The below table is an example of the senario that I am currently working on.

My report is using direct query and should always show live results/data.

I need to be able to count the number of times true or false occurs in the 'available' column depending on the 'asset name' column.

If I was to place the results into a a table it would look like this.

I am unsure if this is relevent to the answer, but I need to place the answer into a card visual, so if I want to display how many times Asset 1 was available, I would need the card to display 2.

Please let me know if there is any further information you need.

Thanks

[amitchandak]

@Anonymous , just a count measure in matrix , Asset on row ,Available on column should do

[Anonymous]

Hi, @amitchandak 

Thanks for getting back to me.

I have writted the following measure and it counts all of the False values in column B.

"Asset 1 True = COUNTAX(FILTER('ASSET',[Available] = FALSE()),TRUE())"

What I need is to be able to count all the False values in column B if it is on the same row as all of the Asset 1 entries in column A.

I am assuming that teh issue is with my measure.

[amitchandak]

@Anonymous ,

A simple COUNTAX('ASSET','ASSET'[Available])  should do as you are taking Available on column

[Anonymous]

@amitchandak Thanks.

I have tried that as well, but all that does is count the number of times a value has been entred into the Available column (column B), It does not take into account if the value is 'False' or 'True' and it does not take into account the value in the Asset colmun (Column A).

I need a count of a spesific value based on the value in another column.

[Anonymous · Accepted solution]

So I have figured out the answer.

 

1: In your visual go to the filters on the right side of the screen and add the column (in this instance Asset Name) to the 'filter on this page' box. select the asset you want to hilight. 

 

2: Open transform data window/power query and format the column (in this instance Available) into a String/Text (If you need the True / False value form this column in another part of the report then duplicate the column ) > Apply & Close

 

start your measure and add

 

3: Count = CALCULATE(COUNTA('Asset'[Available]), FILTER('Asset','Asset'[Available]="1")

 

4: bring the measure into your data card check the answer is correct from your source data

 

This has worked for me hopfully it will help others as well.

 

Edit: I have also found a second method in case the first method does not work for you

 

1: In your visual go to the filters on the right side of the screen and add the column (in this instance Asset Name) to the 'filter on this page' box. select the asset you want to hilight. 

 

2:  add the following in your measure  Count False = COUNTAX(FILTER('Asset'[Available]) = TRUE()),TRUE())

 

This will give you a count of false values, if you need a count of True use the following

Count True = COUNTAX(FILTER('Asset'[Available]) = FALSE()),TRUE())