cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## CREATE A DINAMIC CALCULATED MEASURE

Hi everyone!

I have a table like this:

I want to create a dynamic calculated measure, that counts the number of customers who bought for the first time, based on the filters applied on "Id_Product" and "Sales Date".

 Id_Client Id_Product Sales Date Isabel Shampoo 24/05/2022 Isabel Galletas 03/05/2022 Ivan Galletas 16/02/2022 Ivan Shampoo 21/04/2022 Marlon Café 14/03/2022 Marlon Shampoo 14/04/2022 Marlon Azucar 25/03/2022 Maria Azucar 10/02/2022 Maria Shampoo 21/02/2022

I think the pseudocode to follow is the following:

1. Obtain the data from the "Tb_Table" table, apply the filter established in the report on "Id_Product".

2. Starting from the previous table, make a new table that groups the "Id_Client" and the minimum date of each one of the "Id_Client".

3. The results must be filtered according to the "Sales Date" filter of the report.

4. Count the results obtained and present them in the report.

Examples of results:

1) If you did not place any filter, the result should be 4. Since there are 4 clients, their first service dates being the following: Isabel: 05/03/2022, Ivan: 02/16/2022, Marlon: 03/14/ 2022 and Maria 02/10/2022.

2) If I place the Cookies filter, the result should be 2: Isabel: 05/03/2022 and Ivan: 02/16/2022.

3) If I place the Shampoo filter, and filter date from 04/01/2022 onwards, the result should be 3: Isabel: 05/24/2022, Ivan: 04/21/2022 and Marlon: 04/14/ 2022.

1 ACCEPTED SOLUTION
Super User

not sure if i fully get you, you may try to plot a card visual with a measure like:

``````Measure =
VAR _table =
VALUES(Tb_Table[Id_Client]) ,
"Date",
CALCULATE(MIN(Tb_Table[Sales Date]))
)
VAR _table2 =
_table,
"ID_Product",
CALCULATE(MIN(Tb_Table[ID_Product]))
)
RETURN
COUNTROWS(DISTINCT(_table2))``````

it worked like:

Super User

not sure if i fully get you, you may try to plot a card visual with a measure like:

``````Measure =
VAR _table =
VALUES(Tb_Table[Id_Client]) ,
"Date",
CALCULATE(MIN(Tb_Table[Sales Date]))
)
VAR _table2 =
_table,
"ID_Product",
CALCULATE(MIN(Tb_Table[ID_Product]))
)
RETURN
COUNTROWS(DISTINCT(_table2))``````

it worked like:

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors