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
Baroumy
Regular Visitor

Count a measure results (Text)

Hello folks,

I am struggling with what I thought should be simple. I m ranking the clients as per the below :

Capture3.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The problem is I want to count the the number of clients by theire status.

 

The Dax are as the below:

active sales = if([year of sales] >0 ,1 , "")
Active sales PY = IF([year of sales PY]>0,1,"")
Client status = if([active sales]=1,if([Active sales PY]="","New","Existing"),if([Active sales PY]=1,"lost","Non-active"))
 
Now the [year of sales] is a bit complicated but I ll share it anyway:
 
year of sales = if([Quater No.]=1,[Sales PQ]-[Sales PY (Aging)]+ sum('Client Aging'[Sales Turnover]),if([Quater No.]=2,calculate([Sales PQ],PREVIOUSQUARTER('Calendar'[date])) - [Sales PY (Aging)] + sum('Client Aging'[Sales Turnover]),if([Quater No.]=3,CALCULATE(CALCULATE([Sales PQ],PREVIOUSQUARTER('Calendar'[date])),PREVIOUSQUARTER('Calendar'[date])) - [Sales PY (Aging)]+ sum('Client Aging'[Sales Turnover]),if([Quater No.]=4,sum('Client Aging'[Sales Turnover]),0))))
 
Any ideas about how can I count the clients by their status?
 
Thank you.
 
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Baroumy 
Manually insert a single column disconnect table that contains all statuses (unique list of statuses). Place the new column in a table visual along with the following measure.

Count =
SUMX (
    CROSSJOIN ( VALUES ( 'Table'[Code] ), VALUES ( Status[Status] ) ),
    INT ( [Client Status] = Status[Status] )
)

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Baroumy 
Manually insert a single column disconnect table that contains all statuses (unique list of statuses). Place the new column in a table visual along with the following measure.

Count =
SUMX (
    CROSSJOIN ( VALUES ( 'Table'[Code] ), VALUES ( Status[Status] ) ),
    INT ( [Client Status] = Status[Status] )
)

Hi @tamerj1 

So I should do the first step in the query editor by clicking the "enter data" button and creating a new table named list of status and manually entering the 4 statuses?

 

PS: I need to use the total counts in a single card visual.

@Baroumy 
- Yes
- Yes it should work

@tamerj1 ,

 

Thanks al lot.

 

However the total are wrong, I believe it is due to the [year of sales] and [year of sales PY] measures and the dates filter.

 

Below are the results :

Capture 1.png

 

The existing count is 0! it shoudl be 288. 

@Baroumy 
Try to select a year in a slicer to see what happens. Also I did not understand what is the problem with the total?

*EDIT

Check the spelling of Existing and make sure it matches 100%. It could be a space.

Thank you @tamerj1 . 

The problem was I am relying on the client codes coming from each year's transactions. I added a client list table and I guess the problem is solved.

 

thank you.

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.

Top Solution Authors