Card Visual (Measure with Distinct Count)

[Anonymous]

Hi all,

I need some help creating a measure for a card that call outs Regions in Red. There are only 7 Regions, so the maximum would be 7. Right now, the measure I have created sums up everything that is in Red. In the example below, my Card says there are 7 Regions in Red (7 red icons). This is incorrect. I need it to say 6 Regions in Red with no duplicates of the regions. I basically need a distinct count of the Regions in Red which I am having trouble figuring out how to add to my measure. For reference, Metric 1 and Metric 2 are in seperate tables.

I have tried multiple ways to get what I want, but I'm having trouble. If anyone has any guidance for me, that'd be great.

 

Here's my measure for the card so far:

AFF_RED =

VAR Metric 1_RED = COUNTROWS(FILTER(SUMMARIZE(

'Metric 1', 'Metric 1[Region],"Metric 1", SUM('Metric 1'[Average_Days_COL])), [Average_Days]>33))

VAR Metric 2_RED = COUNTROWS(FILTER(SUMMARIZE(

'Metric 2','Metric 2[Region],"Metric 2",SUM('Metric 2'[Rate_COL])),[Rate]>1))

VAR REGIONS_RED = Metric 1_RED + Metric 2_RED

RETURN IF(ISBLANK(REGIONS_RED),0,REGIONS_RED)

[Anonymous]

HI @Anonymous,

Did these replies help you requirement? If this is a case, you can consider accepting the suitable one as solution to help other users who have the same requirement.
If not, you can feel free to post detailed information to help us clarify your scenario.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi all,

I need some help creating a measure for a card that call outs Regions in Red. There are only 7 Regions, so the maximum would be 7. Right now, the measure I have created sums up everything that is in Red. In the example below, my Card says there are 7 Regions in Red (7 red icons). This is incorrect. I need it to say 6 Regions in Red with no duplicates of the regions. I basically need a distinct count of the Regions in Red which I am having trouble figuring out how to add to my measure. For reference, Metric 1 and Metric 2 are in seperate tables.

I have tried multiple ways to get what I want, but I'm having trouble. If anyone has any guidance for me, that'd be great.

Here's my measure for the card so far:

AFF_RED =

VAR Metric 1_RED = COUNTROWS(FILTER(SUMMARIZE(

'Metric 1', 'Metric 1[Region],"Metric 1", SUM('Metric 1'[Average_Days_COL])), [Average_Days]>33))

VAR Metric 2_RED = COUNTROWS(FILTER(SUMMARIZE(

'Metric 2','Metric 2[Region],"Metric 2",SUM('Metric 2'[Rate_COL])),[Rate]>1))

VAR REGIONS_RED = Metric 1_RED + Metric 2_RED

RETURN IF(ISBLANK(REGIONS_RED),0,REGIONS_RED)

[MFelix]

Believe this post is repeated with the one below:

https://community.powerbi.com/t5/Desktop/Card-Visual-Measure-with-Distinct-Count/m-p/1405570

[Anonymous]

@amitchandak I'm curious if you might have some input you could give me. I'd appreciate any help.

[amitchandak]

@Anonymous , If REGIONS_RED  giving blank then count with values should count red region 

AFF_RED =
VAR Metric 1_RED = COUNTROWS(FILTER(SUMMARIZE(
'Metric 1', 'Metric 1'[Region],"Metric 1", SUM('Metric 1'[Average_Days_COL])), [Average_Days]>33))
VAR Metric 2_RED = COUNTROWS(FILTER(SUMMARIZE(
'Metric 2','Metric 2'[Region],"Metric 2",SUM('Metric 2'[Rate_COL])),[Rate]>1))
VAR REGIONS_RED = Metric 1_RED + Metric 2_RED
RETURN

countx( values('Metric 1'[Region]),REGIONS_RED)

or

RETURN

countx( summarize('Metric 1', 'Metric 1'[Region],"_1",[REGIONS_RED]),[_1])

[PaulDBrown]

@Anonymous 

Can you please provide a dummy dataset or PBIX file to play around with? 

[Anonymous]

Here is the sample data I used. 

As well as that measure for the card:

RED =

VAR METRIC_1 =

COUNTROWS(

FILTER(

SUMMARIZE(

'Metric 1', 'Metric 1'[Region],"1", SUM('Metric 1'[AVG])), [AVG_MEASURE]<=.75))

VAR METRIC_2 =

COUNTROWS(

FILTER(

SUMMARIZE(

'Metric 2','Metric 2'[Region],"2",SUM('Metric 2'[AVG_2])),'Metric 2'[AVG_MEASURE2]<=.75))

VAR GRIDS_RED = METRIC_1 + METRIC_2

RETURN IF(ISBLANK(GRIDS_RED),0,GRIDS_RED)

[Anonymous]

Hi Paul,

Thanks for your willingness to help. I create a sample power bi. How should I send it? I'm not able to attach onto here. 

[PaulDBrown]

@Anonymous 

You can upload the file to a cloud service (Onedrive, Google Drive, Dropbox...) and share the file from there. 
Alternatively you can post sample data in a table format here (not as an image please) so we can copy it and work on a solution.