topic Re: Return dates when condition is met in DAX Commands and Tips

Return dates when condition is met

Anonymous — Tue, 19 May 2020 13:46:25 GMT

Hello Community!

A few days I go I finished an Udemy course "Microsoft Power BI - Up & Running With Power BI Desktop"

Now it's time to use that knowledge it in real life and life is brutal! I have a quite complicated task. I try to calculate kind of user efficiency rate like [quantity/hour] for example I baked 300 doughnuts in 20hours, it gives 15d/h or 1.1kg/h.

1.First I try to take dates from Table1 with code 111.

2.Then using those dates take GUIDs with action1.

3.Having GUIDs I can reach the properties like weight. In the end, the ratio SUM(weight)/SUM(hours) should be available for each user.

Table1 Multiple users, codes and dates.

USER	CODE	HOURS	DATE
User1	111	5	19.05
User2	001	4	19.05
User3	111	5	16.05

....

Table2 Multiple users, actions. . .

USER	ACTION	Object_ID	DATE
User1	create	guid1	19.05
User2	create	guid1	19.05
User3	update	guid2	16.05

....

Table3 unique GUIDs

Object_ID	weight	property1	prop2....
guid1	w1	. . .	. . .
guid2	w2	. . .	. . .
guid3	w3	. . .	. . .

....

My post is dedicated to the first step.

How to get dates from Table1 and use them as a filter to Table2?

In which direction should I go, base only on measures, or new calculated tables? Or it's a too complicated task for PowerBI and I should shape the data with Python first?

Kind Regards

Re: Return dates when condition is met

mahoneypat — Wed, 20 May 2020 00:54:02 GMT

No python code needed here. Power BI can handle it. If there is a relationship between the two tables on the Date column, that might provide your filter. If there is no (or different) relationship, you can pass the dates from Table1 to Table2 with TREATAS()

For example

NewMeasure = Calculate([yourmeasure], TREATAS(Values(Table1[Date]), Table2[Date]))

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Return dates when condition is met

Anonymous — Tue, 26 May 2020 14:13:53 GMT

Sorry for the late reply but I've been busy with this all time. I wanted to try all possibilities before I write another post.
As a first step, I calculated the indexes I want to get in Power BI, using Python in a visual programming software. Now I know what are the values to be expected in Power BI.

(description below screenshots)

The task seems to work when there are no overlapping dates/users. And it also depends which USERS are used in visual (axis), the one from Table1 or Table2.
Like for code Code202. The indexes are correct when I use USERS from Table1. Index is: TotalPartsWeight / SumOfHours, in other notation SUM(weight) / SUM(bookedHours). For other codes the indexes are wrong.

But when I switch to other codes, when multiple users work appear on the same dates, it gets messy.

For example, when I put USERS from Table2 in axis box, I get correct values for 2/3 users. So it must be something wrong with the sum of weight.

On the screenshot below, first user gets Infiniti value because there are no hours for selected codes (division by 0). nvm.
Middle users have correct index, the last one for an unknown reason is incorrect. For him, the value should be

I used USERELATIONSHIP for inactive relationship for calculating the SUM of hours, because when I used just SUM measure from Table1 is was not compatible with Users from Table2.

Between Table1(CSdata) and Table2(Model_H) I set the relationship on dates (Many to Many, Single). Inactive one is on users.

The python algorithm I used for calculating is simple and based on for loops:

For one user it goes as follows
1. First it gets all dates from Table1 under certain code for a user.

2. Then in Table2 it finds GUIDS of elements which were created on those dates by that user.
3. Based on GUIDS from Table2 weight are fetched from Table3.
It's a piece of cake for an algorithm.

I assume the problem lies in the relationship or... filters. Do you have any ideas on how to fix/simplify the operation?
I thought about copying columns from one table to another but the dimensions are completely different so it won't work.

Regards
N

Re: Return dates when condition is met

Anonymous — Tue, 26 May 2020 22:51:26 GMT

Sorry to say that but the way you approach it is completely wrong.

Please, before you start DAXing... learn how to build correct models:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

You should also learn about what DIMENSIONAL DESIGN (star schema) is if you want to build efficient, simple and maintainable models/DAX.

By the way, putting bidirectional filters on all relationships in a model is a sign that you don't understand what it's for and how not to abuse it. Please therefore study dimensional design. What I've written is not to discourage you. We all started with misunderstood concepts abusing DAX and the principles. But now at least you know the way: DIMENSIONAL DESIGN. Study it.

Best
D

Re: Return dates when condition is met

Anonymous — Wed, 27 May 2020 07:24:37 GMT

Thank you darlove for hints and links. I will definetelay watch both of the movies.

The bideractional relationship between the left and the middle table is necessary, without that I get completely wrong weights.
If I set it to one direction, the direction is from left to right. So it is FROM data table TO lookup table. Or... quasi lookup.

In data table are uniqe rows, the properties set for each object. Middle table stores the history of those objects, so it has multiple rown with the same objects, (1st row insertet, 2nd row modiefied, 3rd row modiefid, etc...)

Anyway, I will post in this thread any progress I will manage to make. With propre Tages It might help others in the future.

Regards, Nicram

Re: Return dates when condition is met

Anonymous — Wed, 27 May 2020 08:48:59 GMT

You should really learn about the proper dimensional design and the DANGERS of using bidirectional filters.

This is only one of many articles that can make you aware of potential traps:

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

Best
D