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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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