Forum Discussion

NantoCaparachin's avatar
NantoCaparachin
Frequent Visitor
3 years ago
Solved

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_ClientId_ProductSales Date
IsabelShampoo24/05/2022
IsabelGalletas03/05/2022
IvanGalletas16/02/2022
IvanShampoo21/04/2022
MarlonCafé14/03/2022
MarlonShampoo14/04/2022
MarlonAzucar25/03/2022
MariaAzucar10/02/2022
MariaShampoo21/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.

 

DinamicMeasure
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

     

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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: