cancel
Showing results 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

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

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors