RichardTam Tracker

Measure - Filtered table

RichardTam — Mon, 07 Nov 2022 07:13:53 GMT

Measure.sickLeave = var _lookupaccessLevel=LOOKUPVALUE(accessLevel[Dept],accessLevel[username],USERPRINCIPALNAME()) return calculate(sum(Sick_Leave[Sick Leave]),Sick_Leave[Dept]=_lookupaccessLevel)

I have this measure to calculate the sum of sick days. accessLevel is granted to manager (email accounts) so that they can only view information about their department.

This works fine until I wanted to show more than one variable. ie, if I include sick leave balance, data from other staff will be shown here.

My question is how can I modify the formula so it filter and returns the names of all the employees, so the filtering can be done for all values.

Measure.sickLeave =
var
_lookupaccessLevel=LOOKUPVALUE(accessLevel[Dept],accessLevel[username],USERPRINCIPALNAME())

return

calculate(sum(Sick_Leave[Sick Leave]),Sick_Leave[Dept]=_lookupaccessLevel)

Re: connecting to sharepoint sub folder

RichardTam — Mon, 07 Nov 2022 07:06:31 GMT

hi jer91899,

Correct, these are the current way to get data, problem is lets say: https://sharepoint.com/sites/site belongs to a department, the department has a lot of folders and files.

Power BI has to go to the site, and read every single item within that site directory, before continuing step 4 which is to filter down to the folder you want

Common sense would say you can go to the sub-folder of that site directly, but that is not possible right now.

Combine with the fact that load anything from sharepoint takes way longer than other means. It is literally refresh and forget. Refresh, and forget you are working on it.

Re: connecting to sharepoint sub folder

RichardTam — Mon, 01 Aug 2022 09:25:58 GMT

I understand your pain, it sounds absolutely ludicrous, broderline imcompotent on Microsoft's part that their products are poorly optmised. Combining the way Power Query Editor handles query editing & preview, it is almost impossible to work on anything, in fact, I am waiting for it to load as I'm typing

Re: Merge table created an error row

RichardTam — Mon, 04 Apr 2022 06:59:46 GMT

it is the join column, which is the "Job ID" column
I have checked the table has no errors, so i have no idea how that error appear

would be fine if there is a way to remove the error row within the table,

Merge table created an error row

RichardTam — Mon, 04 Apr 2022 06:00:41 GMT

Refreshing a query i previously wrote with new data, i notice the expand table step causes an error:

An error occurred in the ‘’ query. Expression.Error: The column 'Matching ID' of the table wasn't found. Details: <Matching ID>

and i noticed the table under the merging column has the expected data and an extra error row at the bottom:

wonder how to remove the error row at the bottom, before expanding it

Why can't I create a column chart with all the columns

RichardTam — Wed, 23 Mar 2022 08:54:16 GMT

I just want this in Power BI, how come I can do it in excel, and Power BI is like, wow so many data point, lets give you a scroll bar, column width limited to < 20

Re: An error occurred in the ‘’ query. Expression.Error: The field 'Monthly Time Series' of the record.

RichardTam — Mon, 07 Feb 2022 04:26:55 GMT

Hi Aamir,

I am running into the same problem, have you found a fix yet?
I cant believe how cumbersome it is to make mircosoft products to talk to microsoft services.......

It wont tell me which query, which step or which part is has gone wrong......

Re: Build a new table with the count of one value

RichardTam — Tue, 13 Apr 2021 03:33:03 GMT

Under Edit Query

1. Create unique ID

2. Select all column except unique ID

3. Transform > Any Column > Unpivot Column

4. Home > Close > Close & Apply

Click Matrix under Visualizations

Rows: Attribute

Columns: Value
Values: Count of unique ID

Dar una base de puntuación en el texto

RichardTam — Mon, 31 Aug 2020 10:41:33 GMT

Tengo una encuesta con los siguientes resultados

Respuesta	Q1	Q2	Q3
1	A	A;D	E; F
2	C	B; C	G
3	D	C	H

Supongamos que quería darles una base de puntuación en el texto
como A & G a +1, B,C,D,E,F & H-1

Respuesta	Q1	Q2	Q3	Total
1	1	1-1	-1-1	-1
2	-1	-1-1	1	-2
3	-1	-1	-1	-3

¿Cuál sería la mejor manera de hacer esto, tengo 20 preguntas extrañas

Give a score base on text

RichardTam — Mon, 31 Aug 2020 10:40:29 GMT

I have a survey with the following results

Response	Q1	Q2	Q3
1	A	A;D	E;F
2	C	B;C	G
3	D	C	H

Suppose I wanted to give them a score base on the text
like to A & G = +1, B,C,D,E,F & H=-1

Response	Q1	Q2	Q3	Total
1	1	1-1	-1-1	-1
2	-1	-1-1	1	-2
3	-1	-1	-1	-3

What would be the best way of doing this, I have 20 odd questions

Re: Filtre un valor de la segmentación de datos para encontrar el promedio

RichardTam — Mon, 20 Jul 2020 09:45:26 GMT

Tengo una lista de personal para elegir en la cortadora, todos pertenecen a uno de cuatro tipos de sub.
Sólo quiero obtener un promedio del tipo sub, durante cada mes, en comparación con el rendimiento real de ese personal

Así que me gustaría que el total de Sub tipo 1 se agregue al gráfico

Re: Filter a value from slicer to find the average

RichardTam — Mon, 20 Jul 2020 09:44:14 GMT

I have a list of staff to choose in the slicer, they all belong to one of four sub type.
I just want to get an average of the sub type, over each month, compare to the actual performance of that staff

So I would like the total of Sub type 1 to be added into the chart

Re: Filtre un valor de la segmentación de datos para encontrar el promedio

RichardTam — Mon, 20 Jul 2020 08:22:34 GMT

Entiendo la fórmula, pero estoy tratando de encontrar el promedio de ese tipo de personal.
decir que la cortadora seleccionó "A"
Me gustaría ver el número medio de tareas creadas por las ventas

Re: Filter a value from slicer to find the average

RichardTam — Mon, 20 Jul 2020 08:21:30 GMT

I understand the formula, but I am trying to find the average of that staff type.
say the slicer selected "A"
I would like to see the average number of task created by sales

Filtre un valor de la segmentación de datos para encontrar el promedio

RichardTam — Mon, 20 Jul 2020 07:19:22 GMT

Digamos que tengo una consulta de personal:

Personal	Sub-tipo
A	Ventas
B	Ventas
C	Back Office
D	Back Office
E	Ventas

Y tengo una tabla de tareas:

ID de tarea	Fecha de creación	Creado por	Terminado por
1	1/07/2020	A	B
2	2/07/2020	B	B
3	2/07/2020	A	C
4	4/07/2020	C	A
5	4/07/2020	D	A
6	8/07/2020	B	D
7	8/07/2020	A	A
8	8/07/2020	D	C
9	10/07/2020	E	C
10	12/07/2020	E	D

Cada pentagrama crea y termina algunas tareas. Ahora estoy tratando de ver los informes de cada personal
Así que el informe tendrá una cortadora para el personal (es decir, el personal B)
y 2 gráficos me mostrarán el número de tareas creadas y terminadas por B

En este momento estoy tratando de agregar un promedio para los dos gráficos,
así que puedo ver el promedio del grupo, es decir, cuántos puestos de trabajo se crearon a partir de ventas, el cálculo rápido puede filtrar un sub tipo o personal en particular, ¿cómo hacer que lea lo que sea de la segmentación de datos?

Muchas gracias

Filter a value from slicer to find the average

RichardTam — Mon, 20 Jul 2020 07:18:16 GMT

Lets say I have a staff query:

Staff	Sub type
A	Sales
B	Sales
C	Back Office
D	Back Office
E	Sales

And I have a task table:

Task ID	Created Date	Created by	Finished by
1	1/07/2020	A	B
2	2/07/2020	B	B
3	2/07/2020	A	C
4	4/07/2020	C	A
5	4/07/2020	D	A
6	8/07/2020	B	D
7	8/07/2020	A	A
8	8/07/2020	D	C
9	10/07/2020	E	C
10	12/07/2020	E	D

Each staff creates and finish some tasks. I am now trying to look at reports of each staff
So the report will have a slicer for staff (ie Staff B)
and 2 charts will show me the number of tasks created and finished by B

Right now I am trying to add in an average for the two charts,
so I can see the average of the group, ie how many jobs was created from sales, quick calculation can filter a particular Sub type or Staff, how to make it read whatever from the slicer?

Many thanks

Exportar varias páginas se basan en el número de elementos únicos

RichardTam — Wed, 15 Jul 2020 10:21:02 GMT

Así que tengo una página sobre el rendimiento individual del personal, y me gustaría exportar la página para todos ellos, ¿hay una buena manera de hacerlo?

Export multiple pages base on number of unique item

RichardTam — Wed, 15 Jul 2020 10:19:57 GMT

So I have a page about individual staff's performance, and I would like to export the pagefor all of them, is there a good way to do it?

Re: Múltiples gráficos de línea

RichardTam — Wed, 15 Jul 2020 10:17:27 GMT

esa es la cosa, no sé se ha hecho o no

Re: Multiple charts of line

RichardTam — Wed, 15 Jul 2020 10:16:23 GMT

that's the thing, I don't know has it been done or not