Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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