Distinct Count value to consumer

[Anonymous]

Hello,

I have a table and im actually trying to make it into a bar chart as well. I want a bar chart by staff name to show in the last 90 days a value of seen or not seen. The issue i am having is some of the consumers have a blank and a value of seen but since they were seen in the last 90 days it counts as seen and not seen. Its not distinct. I guess i am having trouble making them distinct. Below are a couple of examples i have from an excel table but in powerbi they are similar. The top table is similar to what i am working with and the bottom is what i am looking for. So if in the last 90 days consumer 1 was seen twice but even though he had a blank or was not seen a different time in the last 90 days i still want it to = Seen or Y distinctively to that consumer as they were seen in the last 90 days one time. If not seen in the last 90 days at all then N or Not Seen.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Datediff = DATEDIFF([date],TODAY(),DAY)

Flag =
COUNTX(FILTER(ALL('Table'),'Table'[Datediff]<=90&&'Table'[Consumer]=EARLIER('Table'[Consumer])&&'Table'[Seen] in {"Y",BLANK()}),[Consumer])

2. Create measure.

Seen/Not Seen =
IF(
    MAX('Table'[Datediff])<=90 && MAX('Table'[Flag])>=1,"Y","N")

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Create a measure like this and try

Measure =
var _cnt = CALCULATE(Count(Sales[Customer]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY), filter(Table, Table[seen] ="Y"))
return
if(isblank(_cnt) ="N" , "Y")

[Anonymous]

I cant seem to get it to work. The filter for looking back i have a seperate date field in the actual filters column looking back 90 days. I believe it is a formatting issue.

[amitchandak]

@Anonymous , one mistake was there it should be -90

Measure =
var _cnt = CALCULATE(Count(Sales[Customer]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY), filter(Table, Table[seen] ="Y"))
return
if(isblank(_cnt) ="N" , "Y")

if you already have measure that can look back 90 days you can use that

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

I created a sample i can share. I can't share my actual report as it does have sensitive content in it. I guess i am unsure how to share it with you however.