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
VitHorak
Frequent Visitor

Measure that returns count of occurrences of strings that are result of anothe measure

Hi,

 

I will appreciate any help with following issue...

 

I have a table of employees (id, name, date of entry) and table of projects completed with succes by these emyployees.

These tables are coonected via id of that employee. 

 

I have a measure (lets named it "Performace status") that return three kind of strings based on selected date in slicer visual.

For exapmle if I select one date in slicer (need to select just and exactly only one date), this measure give me a string values to all of the employees like "Active", "Nonactive" and "New" based on their performace (count of succes projects) according to the selected date (no matter how this is calculated). If I put this measure in matrix, everything works perfectly.

 

So if I choose for example 2022-01-01 in a slicer, matrix visual show me all employees in first column (rows of matrix) and in second column (values) I have string values telling me their performace to the date of 2022-01-01...

Something like this:

Employee idPerformace status
1Active
2Nonactive
3New
4Nonactive
5Active

 

 

And my issue...

Measure "Performace status" have a variable inside of dax: VAR _selectedDate = ALLSELECTED('Calendar'[Date]) which enable me to count further in the dax code other things (count of succes projec by their dates) that give me a result of performace status. But it gives me a result limited just for only one date! If I choose two dates in slicer or remove slicer, measure failed with error. It is because of allselected function...

 

Is it possible to change this matrix and write another one dax measure to count the number of Performance statuses by all of dates?  And I need to have something like this:

 

Performace status2022-01-312022-02-282022-03-31
Active5108
Nonactive10710
New210

 

 

In short... One dynamic measure give me statuses (strings) according to one selected date and I need second one that gives me count of result of first measure by all dates.

 

 

Measure "performace status" look like sometging like this:

Performace measure = 
VAR _selectedDate = ALLSELECTED('Calendar'[Date])
VAR _IDemployee = MAX(Emyployee[id])

VAR _newFrom = MIN(Employee[Date of entry])
... another few VARs that give a dates to give me a milestones to calculate a performance... like _newFrom  + 1 month atc...

 

RETURN

IF( conditions.. , "New", IF( conditions..., "Active", IF(conditions..., "Nonactice",.... and so on and if all condition are not met, give a "Error" string...)

 

Thanks a lot for any tips or help!!!!

 

 

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @VitHorak 

you need to have a separate disconnected table that contains all the unique string values ("Active", "Noneactive", "New") then use it in the rows of the matrix. The measure would be

SUMX (

VALUES ( 'Table'[Employee ID] ),

IF (

[Performace status] = SELECTEDVALUE ( 'Status'[Status] ),

1

)

)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @VitHorak 

you need to have a separate disconnected table that contains all the unique string values ("Active", "Noneactive", "New") then use it in the rows of the matrix. The measure would be

SUMX (

VALUES ( 'Table'[Employee ID] ),

IF (

[Performace status] = SELECTEDVALUE ( 'Status'[Status] ),

1

)

)

Perfect!!! Thank you very much. It is so easy If somebody kicks your brain! :)) Thanks a lot.

amitchandak
Super User
Super User

@VitHorak ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Need better data also.
Appreciate your Kudos.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors