Combine TREATAS with Calculate function

[JustinDoh1]

I am sharing Pbix file here in this Google Drive.

Also, please note that I modified the original code and updated code is reflected on this post.

So, some of code mentioned on the bottom discussion might confuse you because the original code has been updated.

I am trying to apply TREATAS as below (in order to create a virtually relationship), but when I uncomment (apply) these lines where TREATAS are, output comes out to be blank.

Expected outcome should be:

79.33 for CA and 126 for CO as below:

How do I apply TREATAS properly in Calcuate here?

Thanks for help!

[JustinDoh1]

@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!

[Element115]

By the way, as a side note, looking at your model, and I should say, I am not by far an expert yet in DAX or Power BI, but nonetheless, my understanding of modeling tells me that in this case you are showing us, having a column with unique values in both tables you linking to via the bridge table, would be a good thing to have. Something like a unique key, ie what in the DB world they call a unique primary key, like an ID of some sort. If possible.  The idea also being to avoid as much as possible many-to-many relationship and favor instead one-to-many relationships as much as possible.

Which also means, the most important part of the work is modelling. Screw the model up, ie not understanding how to categorize the source data and how to link it in a way that is conceptually logical and sensical for the problem domain, and you will find yourself in a world of pain real fast when trying to code DAX measures. I know, I learned the hard way ;-). 

So getting the DB side right first: ideally pushing all your data to a data warehouse structure as opposed as getting from a OLTP relational DB (data structure is different).

Then import and ETL becomes easier too, and easy modelling naturally flows from there. 

Good luck!

[Element115]

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.

[JustinDoh1]

@Element115 Thank you. Actually, the output comes out to be nothing after adding ','

I think case is dealing with a condition, and this might be different case than the example from https://dax.guide/treatas/ . 

[Element115]

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! 😉

[JustinDoh1]

@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! 

[Element115 · Accepted solution]

No worries.  Did you see my response here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Combine-TREATAS-with-Calculate-function/m-p/1...

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.

[JustinDoh1]

@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!

[Element115]

The first thing I notice in your code is that there is a missing ',' after 

CurrentTestValue = Index[IndexRating]

just before the next line where TREATAS is.  Could that be generating the error?  

Because when I go to https://dax.guide/treatas/  and look at the examples given there, the patterns of using TREATAS in the filter section of CALCULATE seem to be exactly the same as what you are doing.  So... if we were dealing with a normal programming language here 😉 , I would say it should all be good (Are you listening Microsoft?).  But DAX being DAX, I will have to say:  The code looks like it should work, so who the he11 knows!?  

Tongue in cheek statement, cuz I've been there. And even after you think you finally understood how DAX behaves, you still get hit with something that on a syntactic level makes perfect sense, but for God knows what mysterious reason, refuses to compute due to the occult nature of the DAX compute engine for which I have found no clear and short step-by-step explanation anywhere.   You can find it for C# CLR VM, or Java's VM, but the DAX engine, good luck--it does what the f*ck it wants and expects you to somehow understand it through some shamanic vision or something.