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
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
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