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
NantoCaparachin
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_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.

 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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:

FreemanZ_0-1684250926949.png

FreemanZ_1-1684250949468.png

FreemanZ_2-1684250971941.png

 

View solution in original post

1 REPLY 1
FreemanZ
Super User
Super User

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:

FreemanZ_0-1684250926949.png

FreemanZ_1-1684250949468.png

FreemanZ_2-1684250971941.png

 

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.

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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