Userealtionship DAX

[Anonymous]

HI Experts

I have teh following DAX measure using a switch function, i want to add userelationship to this measure... not sure if it is possible.

B1 Actuals = 
VAR SummaryItem = TRIM(SELECTEDVALUE( 'Templates'[Summary 4] ))
    Return

SWITCH(TRUE(),
      
    SummaryItem = "U.S.", FORMAT(CALCULATE([Total Revenue], FILTER(Management,Management[Level7] ="TOTAL U.S.")),"$#,##0.0;($#,##0.0)"),
    SummaryItem = "Canada", FORMAT(CALCULATE([Total Revenue], FILTER(Management,Management[Level6]= "Canada")),"$#,##0.0;($#,##0.0)"),
                  BLANK(),USERELATIONSHIP(Management[Level4],Templates[Summary 4]))

[v-yulgu-msft]

Hi @Anonymous,

There is no any relationship betweent 'Templates' table and 'Management' table, right? In such a scenario, there is no need to use USERELATIONSHIP function in the measure. Please see below:

B1 Actuals = 
VAR SummaryItem = TRIM(SELECTEDVALUE( 'Templates'[Summary 4] ))
    Return

SWITCH(TRUE(),
      
    SummaryItem = "U.S.", FORMAT(CALCULATE([Total Revenue], FILTER(Management,Management[Level7] ="TOTAL U.S.")),"$#,##0.0;($#,##0.0)"),
    SummaryItem = "Canada", FORMAT(CALCULATE([Total Revenue], FILTER(Management,Management[Level6]= "Canada")),"$#,##0.0;($#,##0.0)"),
                  BLANK())

Best regards,

Yuliana Gu

[AlB]

Hi @Anonymous

USERELATIONSHIP can only be used in functions that take a filter predicate as an argument. See here

If you explain what you are trying to do we can give it a try.

Additionally, what is your intention in using the BLANK() as the last 'value' argument  of the SWITCH() ??

[Anonymous]

Thanks AIB

I am trying to filter the data in the table based on the selections in a filter, i am using a template uploaded from excel in Power BI, currently the template has been added to the data model as a discount table.

So by creating a inactive relationship between template table and the management level table i should be able to filter the data in the table????

[AlB]

@Anonymous

First of all, I do no think the last option with Blank is ever going to execute. You are doing SWITCH(TRUE() and blank is never equal to TRUE.

Regarding USERELATIONSHIP, you use it to temporarily activate an existing inactive relationship.

[Anonymous]

Many thanks