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 numb...
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())
Anonymous ,
A simple COUNTAX('ASSET','ASSET'[Available]) should do as you are taking Available on column
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.
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())