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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors