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.
Element115 First of all, thank you for helping me.
I tried with "Related", but it give me an error.
BTW, you could try with a Pbix file that I am sharing on the Google drive.
As far as I know, TREATAS is needed to create a virtual relationship because they are many-to-many relationship of (..Rating and ProcessinDate). I got a help for the original DAX code from this post that I asked earlier regards to this.
So, going back to idea of not using TREATAS, I guess what I could do is creating a combined column (by merging two columns) and create one column to connect two tables. I am trying to decide what makes more sesnse (to create a merged column in PowerBI, to create a merge column in SQL (data source) or wait for possible solution with code change).
I am leaning toward code change only (waiting for advice) because I could learn for possibility, but I also think that merging columns might be a good practice...
Thanks!
Well... as I suspected, TREATAS is not necessary. I modified your code as follows and it returns the results you said you want to see--basically I got rid of TREATAS--here:
Col4 = IF(
HASONEVALUE( Main[Name]),
var CurrentTestValue =
(
IF
(SELECTEDVALUE(Main[CurStar]) = 5
,5
,
SELECTEDVALUE(Main[CurStar]) + 1
)
)
return
(
CALCULATE
(
MAX ( Index[IndexPoint] ),
CurrentTestValue = Index[IndexRating]
// TREATAS ( VALUES ( Main[MainRating] ),
// Index[IndexRating]
//)
)
)
)
The way I understand all this is that since we are performing a CALCULATE on table Index, using only columns from table Index, then DAX should know that both columns belong to the same table and thus why should there be an issue?
Furthermore, since the dynamically calculated value inside the variable should resolve to a scalar, why does it matter where the data used to compute it comes from? I say it should not matter. In other words, how the data is computed based on table and column relationships for that particular data, of course, falls under the same rules as everything else in DAX. But if those rules are respected, we should get a scalar value in the end.
Then, comparing a scalar value in an equality test to a scalar value contained in some column for the same table on which we CALCULATE a MAX on a column from again the same table, should not pose any issues due to relationship or cardinality or god knows what else.
But am sure there is a million of other esoteric scenarios that could be involved here, somehow, and that, only the 2 gods of DAX, Alberto Ferrari and Marco Russo would be able to explain in detail. And I mean, in detail ;-), as is their wont.