Variables table using CALCULATETABLE with condition

[Jaggana]

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?

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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.

[MFelix]

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]

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

[MFelix]

Hi @Jaggana ,

If you don't provide a complete sample of your code it's difficult to pinpoint where you may need to change the formulas in order to get the correct value, depending on the context of the calculation a lot of changes may or not be needed.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[v-nmadadi-msft]

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

[Jaggana]

Hello @v-nmadadi-msft, 
As I can't upload a PBIX, CSV or TXT file, I've added the data in the initial post

[MFelix]

Hi @Jaggana ,

The sample data you provided is similar to the one I have created and based my measure on. Do you have any others aspects that can impact this? You refer that its more complex.

What is the final result you need.

You can upload a file using WeTransfer OneDrive or Google drive .