CREATE A DINAMIC CALCULATED MEASURE

[NantoCaparachin]

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.

[FreemanZ · Accepted solution]

hi @NantoCaparachin 

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

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

it worked like: