Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Return dates when condition is met

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 hav...
condition
dates
Filter
return
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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