Forum Discussion
Combine TREATAS with Calculate function
No worries. Did you see my response here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Combine-TREATAS-with-Calculate-function/m-p/1953210/highlight/true#M42622
Basically I commented out your TREATAS and got the results you wanted as per your first post here.
I do not know what the semantics of your model should be like, but like you said in your last post, there is no direct relationship between the 2 table Main and Index. Even so, it is not necessary to perform your computation, as I have shown in my response at the link above.
Now, if on the other hand, you need some sort of relationship between Main and Index, then use everything you know from SQL re how to design a proper ER diagram. Those principles translate well to designing a star- or snowflake schema in Power BI. In fact, it's actually easier to design a star schema with Dim or Lookup tables on top in the modeling space, and the Fact tables underneath the Dim tables.
Fundamentally, if you are gonna have to deal with date and/or time, then you need to make sure you first create a well-formed DimDate table and/or a DimTime table. You can do it in M in Power Query Editor, or in DAX. I do it in M usually because that way I can parameterize the table creation. Google it and you'll find the code for both options online.
Once you have that, you need to think about the semantic relationship between you data and that will help come up with the Fact tables and what the relationships among them should be. And so on.
Sorry, it seems I can neither read nor write properly today. Let's ignore my previous post and let's start again with your code:
CALCULATE(
MAX( Index[IndexPoint] ),
CurrentTestValue = Index[IndexRating]
)
MAX is applied on table Index. The filter is also applied to table Index, but using a dynamically computed value based on values from another table, table Main.
OK... now... I say there is no need for TREATAS. Tables Main and Index do not even neet to be related. Unless... and here I forget... but the measure could be saved in any table for that matter, in fact even in an empty table that only serves as a measure container. So what I do not remember is whether or not where, that is, in which table is the measure stored, does that influence how the measure is executed in terms of column scope and so on?
In other words, if a measure stored in Main table, references Index table columns, do we need a relationship between both tables or not. I can't remember atm. But try with or without. And if a relationship is needed, then let's make the relationship direction go from Main to Index. And if that does not do the trick, let's write
MAX( RELATED( Index[IndexPoint] ) )
and let's write:
CurrentTestValue = RELATED( Index[IndexRating] )
and if that does not work... then let's ask somebody else! 😉
Element115 Thanks for your help again. I tried with modifying the relationship of two tables, but at this point, it breaks down all previous calculations, so I decided to stay with current realtionship, and work around with current code that I have.
I decied to go back to my original code logic, and I updated my original code and it is reflected on the original post here. Sorry if it is inconvinient for you.
Please refer to my Pbix file to take a look at the relationship that I have. Adding "related' on those two lines would not work because there is no relationship. I agree that DAX is not straightforward as SQL so I guess I just have to practice with grammars of these functions (calculate, treatas etc..) to get used to. If you have any solution please let me know. Thanks again!
No worries. Did you see my response here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Combine-TREATAS-with-Calculate-function/m-p/1953210/highlight/true#M42622
Basically I commented out your TREATAS and got the results you wanted as per your first post here.
I do not know what the semantics of your model should be like, but like you said in your last post, there is no direct relationship between the 2 table Main and Index. Even so, it is not necessary to perform your computation, as I have shown in my response at the link above.
Now, if on the other hand, you need some sort of relationship between Main and Index, then use everything you know from SQL re how to design a proper ER diagram. Those principles translate well to designing a star- or snowflake schema in Power BI. In fact, it's actually easier to design a star schema with Dim or Lookup tables on top in the modeling space, and the Fact tables underneath the Dim tables.
Fundamentally, if you are gonna have to deal with date and/or time, then you need to make sure you first create a well-formed DimDate table and/or a DimTime table. You can do it in M in Power Query Editor, or in DAX. I do it in M usually because that way I can parameterize the table creation. Google it and you'll find the code for both options online.
Once you have that, you need to think about the semantic relationship between you data and that will help come up with the Fact tables and what the relationships among them should be. And so on.
Element115 Somehow, when I tried your code again, it worked. I am going to research why it did not work last time when I tried.. Thanks!