Forum Discussion

Jaggana's avatar
Jaggana
Advocate I
9 months ago

Variables table using CALCULATETABLE with condition

Hello,

 

Here is my test source: 

SalesTerritoryKey	Country
1	United States
2	United States
3	United States
4	United States
5	United States
6	Canada
7	France
8	Germany
9	Australia
10	United Kingdom
11	Corporate HQ
12	yy
13	zz

 

I am not able to store a table using CALCULATETABLE into a variable with a condition, I don't understand what's wrong.

The bellow code has no sens because I simplied it a lot, it's normal.

 

The error is on the last line: The function expects a table expression for argument '', but a string or numeric expression was used.

VAR AllValues = CALCULATETABLE(VALUES(SalesTerritory[Country]))
VAR Country =
    IF(
        HASONEVALUE(SalesTerritory[Country])
        , CALCULATETABLE(
            ROW("Country", SELECTEDVALUE(SalesTerritory[Country]))
        )
        , AllValues
    )

RETURN
    SWITCH(TRUE(), "Australia" IN Country,  TRUE(), BLANK())

 

When I remove the condition or just executing the else condition, it works:

VAR AllValues = CALCULATETABLE(VALUES(SalesTerritory[Country]))
VAR Country = AllValues

RETURN
    SWITCH(TRUE(), "Australia" IN Country,  TRUE(), BLANK()

or

VAR AllValues = CALCULATETABLE(VALUES(SalesTerritory[Country]))
VAR Country =
	CALCULATETABLE(
		ROW("Country", SELECTEDVALUE(SalesTerritory[Country]))
	)

RETURN
    SWITCH(TRUE(), "Australia" IN Country,  TRUE(), BLANK())

 

(edit, I also tried with a SUMMARIZECOLUMNS, removing the CALCULATETABLE, the issue is the same)

How can I apply a condition on the content of my variable table Country?

Need Help

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 months ago

    Hi Jaggana ,

     

    To what I can see from the code you want to return true if Australia is on the values of the selected countries just simply use the values, there is no need to use the CALCULATETABLE since the values will return a single row or several rows depending on your filters

    Selected country = 

VAR Country = 
 VALUES(SalesTerritory[Country])

RETURN
   SWITCH(TRUE(), "Australia" IN Country,  TRUE(), BLANK())

     

     

    If this is not the expected result please let me know what is the purpose of this calculation so I can let you know how to updated.

     

     

     

     

    • Jaggana's avatar
      Jaggana
      Advocate I
      9 months ago

      Thank your for your time, but as I wrote I drastically simplified the code, what I really need is to condition the value of the variable between 2 variable tables

      • v-nmadadi-msft's avatar
        v-nmadadi-msft
        Community Support
        9 months ago

        Hi Jaggana ,

        Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

        Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

        Please show the expected outcome based on the sample data you provided.
        Thank you

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    9 months ago

    Hi Jaggana ,

    I wanted to check if you had the opportunity to review to provide the necessary information as requested. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    9 months ago

    Hi Jaggana ,

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you