Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
giorgiokatr
Helper V
Helper V

count customers with digital transaction ratio bigger than in a date period

i have this table

 

customerdatetransaction typeamount
11/1/2022digital10
11/2/2022digital20
11/3/2022digital30
11/4/2022digital11
11/5/2022digital48
11/6/2022digital69
11/1/2022physical45
11/2/2022physical85
11/3/2022physical96
11/4/2022physical47
11/5/2022physical6
11/6/2022physical22
21/1/2022digital10
21/2/2022digital20
21/3/2022digital30
21/4/2022digital11
21/5/2022digital48
21/6/2022digital69
21/1/2022physical45
21/2/2022physical85
21/3/2022physical96
21/4/2022physical47
21/5/2022physical6
21/6/2022physical22
21/1/2022digital10
21/2/2022digital20
21/3/2022digital30
21/4/2022digital11
21/5/2022digital48
21/6/2022digital69
21/1/2022physical45
21/2/2022physical85
21/3/2022physical96
21/4/2022physical47
21/5/2022physical6
21/6/2022physical22

for each customer i want to calculate the ratio digital / total transactions (digital+physical) amount in the last 3 months

a customers that has ratio >0.5 (digital transactions more than 50% of his total transactions is a digital customer

I want when selecting a month form a slicer (example 01/06/2022) to count the customers of this month that have a >0.5 in the last 3 months of this selection.

2 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1666801972121.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

=
COUNTROWS(
    FILTER(
        VALUES( TXN[customer] ),
        CALCULATE(
            CALCULATE( [Total], TXN[transaction type] = "digital" ) / [Total],
            DATESINPERIOD( DATES[Date], MAX( DATES[Date] ), -3, MONTH )
        ) > .5
    )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
giorgiokatr
Helper V
Helper V

can the dax be modified so it shows in a card the distinct count of customers for this? (>0.5)?

To obtain the same visual effect as Card,

CNENFRNL_0-1666893610810.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

I would like to count the customers. Example 4 customers in this month had digital ratio (3 month  transactions) >0.5

=
COUNTROWS(
    FILTER(
        VALUES( TXN[customer] ),
        CALCULATE(
            CALCULATE( [Total], TXN[transaction type] = "digital" ) / [Total],
            DATESINPERIOD( DATES[Date], MAX( DATES[Date] ), -3, MONTH )
        ) > .5
    )
)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

giorgiokatr
Helper V
Helper V

Thanks worked great!

CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1666801972121.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.