Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Userealtionship DAX

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]))
Need Help

5 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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