Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jacob2102
Helper II
Helper II

Datos únicos del año sin incluir datos alcanzados años anteriores

Hello everyone,

 

I'm developing a participation analysis, and I would like to obtain 2 different measures:

  1. Number of unique companies involved in the activity, in the selected period.
    1. DAX = DISTINCTCOUNTNOBLANK(Participation[Id_Entity] (associated with the date of the activity).
  2. Number of unique companies involved in the activity in the selected period, and that have not involved in previous periods.
    1.   DAX = ??? (associated with the date of the activity).

How can I get the second measure? 

The analysis is for four years, and I need to know each selected period the new participating companies.

 

Thanks.

Regards,

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@jacob2102 , refer I have used customer ID and Isblank and monthly measures, Same can help you to build

 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

View solution in original post

v-yangliu-msft
Community Support
Community Support

Hi  @jacob2102 ,

Is your problem solved, you can also try the following:

I created some data:

vyangliumsft_0-1648540833896.png

Here are the steps you can follow:

1. Create calculated table.

vyangliumsft_1-1648540833899.png

2. Create measure.

Flag =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
IF(
    MAX('Table'[date])>=_min&&MAX('Table'[date])<=_max,1,0)
Number of participants =
CALCULATE(DISTINCTCOUNT('Table'[companies]),ALLSELECTED('Table'))
Number of non-participants =
var _all=CALCULATE( DISTINCTCOUNT('Table'[companies]),ALL('Table'))
return
_all -[Number of participants]

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1648540833900.png

4. Result:

vyangliumsft_3-1648540833901.png

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

 

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

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @jacob2102 ,

Is your problem solved, you can also try the following:

I created some data:

vyangliumsft_0-1648540833896.png

Here are the steps you can follow:

1. Create calculated table.

vyangliumsft_1-1648540833899.png

2. Create measure.

Flag =
var _min=MIN('Date'[Date])
var _max=MAX('Date'[Date])
return
IF(
    MAX('Table'[date])>=_min&&MAX('Table'[date])<=_max,1,0)
Number of participants =
CALCULATE(DISTINCTCOUNT('Table'[companies]),ALLSELECTED('Table'))
Number of non-participants =
var _all=CALCULATE( DISTINCTCOUNT('Table'[companies]),ALL('Table'))
return
_all -[Number of participants]

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1648540833900.png

4. Result:

vyangliumsft_3-1648540833901.png

If I have misunderstood your meaning, please provide your desired output and your pbix without privacy information.

 

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
Super User
Super User

@jacob2102 , refer I have used customer ID and Isblank and monthly measures, Same can help you to build

 

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Thanks for your answer.

 

I am going to create an aditional column to identify the first participation date and then create a relationship to exclud the dates.

 

Thanks.

Regards,

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.