Forum Discussion
Card Visual (Measure with Distinct Count)
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:
9 Replies
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_REDRETURN IF(ISBLANK(REGIONS_RED),0,REGIONS_RED)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
Can you please provide a dummy dataset or PBIX file to play around with?
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.
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_2RETURN IF(ISBLANK(GRIDS_RED),0,GRIDS_RED)
amitchandak I'm curious if you might have some input you could give me. I'd appreciate any help.
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
RETURNcountx( values('Metric 1'[Region]),REGIONS_RED)
or
RETURN
countx( summarize('Metric 1', 'Metric 1'[Region],"_1",[REGIONS_RED]),[_1])
