Creating a card using sql query

[tina345]

Hello Experts,

  I have a data like below which is from a database sql query.

  I need to use a card to display counts for each code (A,B in this case) in this query.

  What is the best approach to display counts on cards?

 Do I need to create separate measure for each code and use rowcount with filter using code? Or  we have something so that we don't need to create measure for each code (A,B)?

Code,Value,Date

------------------

A, 1, 7/1/2021

A,2, 7/2/2021

B,5,6/1/2021

B,6,7/2/2021

B,7,7/3/2021

[v-easonf-msft · Accepted solution]

Hi, @tina345 

Solution1:
Create  separate measure for each code and put them to card visual

CountA = 
CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Code] = "A" ) )

CountB = 
CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Code]="B") )

Solution2:

Put field "Value" to card visual and then change the aggregation from "Sum" to "Count".

Put field "Code" to visual filter pane and select "Basic filtering" to filter data you want.

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason