topic Re: Calculated column to mark database with 1 and 0 based on a measure in DAX Commands and Tips

Calculated column to mark database with 1 and 0 based on a measure

Anonymous — Fri, 06 Mar 2020 15:32:24 GMT

Hi everyone! I need help with a calculated column that marks with 1 when a person with a certain kind of Function worked 7 shifts on a week. I found a way to calculate this with a measure:

M7HT = COUNTROWS( FILTER( SUMMARIZE(BD,BD[Week],BD[WorkerID] ,"Count7",COUNT(BD[WorkerID])) ,[Count7]=7 ) )

With this measure everythings works great, and I can cover the Function problem by filtering my reports with let's say Function ="X"
So the thing is, I need to pass this to a calculated column because I need to visualize the date when the person with the X Function worked the 7th shift, I need to visualize how many times on a month the person worked a 7th shift, I need to know the average of 7th shifts that all workers with the "X" Function did on a certain period, etc. And the measure doesn't solve all that. Could you please help me? I can't figure it out and I really need it. I think this has to do with iterators? If you can also help me with some content about that subject to learn more it would be great,

Thank you for your time,
Best regards,
Gabriela

Re: Calculated column to mark database with 1 and 0 based on a measure

Greg_Deckler — Fri, 06 Mar 2020 15:42:57 GMT

Need some more information, what table are you creating this in? Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Calculated column to mark database with 1 and 0 based on a measure

Anonymous — Fri, 06 Mar 2020 15:58:50 GMT

Hi Greg_Deckler sorry I forgot to give more context, right now I can't share you the data, but the table looks like this

WorkerID	Function	Date	Shift	Week
A	X	01-01-2020	1	1
A	X	02-01-2020	2	1
B	Y	01-01-2020	1	1
B	Y	01-01-2020	2	1

Basically you have a row for everytime the worker worked on a certain date doing a certain function. There are three shifts every day, so the person can work more than two times a day, I don't know if that information is relevant, but that's pretty much the whole picture, I hope the scenario is clearer,

Re: Calculated column to mark database with 1 and 0 based on a measure

Greg_Deckler — Fri, 06 Mar 2020 16:06:52 GMT

OK, so is that your BD table?

If that is the case, you might need to do something like:

M7HT Column = VAR __WorkerID = [WorkerID] RETURN CALCULATE([M7HT],FILTER(ALL('BD'),[WorkerID]=__WorkerID))

When this measure "works", how are you displaying it? What other columns/tables are in the visualization with the measure?

Re: Calculated column to mark database with 1 and 0 based on a measure

Anonymous — Fri, 06 Mar 2020 16:28:44 GMT

Yes, that's the BD table. To see the measure working I usually see the total of 7th shifts worked month by month, or the quantity of people who did more than 7 shifts on a week v/s the ones that worked less than 6, so I basically use the same Date hierarchy and also I filtered the workers with the X Function, so there are no more columns/tables involved. I tried the formula, but it didn't worked, it displays a repeated number, not the 1s and 0s as I need and it also doesn't take account of the X Function in its calculation,

Greetings!