Forum Discussion
(Direct Query) Count Values in column B depending on the value in column A
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
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())
5 Replies
Anonymous , just a count measure in matrix , Asset on row ,Available on column should do
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.
Anonymous ,
A simple COUNTAX('ASSET','ASSET'[Available]) should do as you are taking Available on column
