In a textbox dynamically display all country names where a measure% > 0

[Sania-F]

I have a dim country table with column "country name", I have a measure table with measure as "EVA%". I need to dynamically display all country names in a text box wherever the EVA% is greater than 0. There is no relationship between measure table and dim country table.

I tried using below DAX in measure table , but getting error as -'EVA%' is not recognized or available in the current context.

.
after googling realized that , MAX function doesnt take measure but takes column only.

   ```DAX

   EVA% Greater Than Zero = IF(MAX('MeasureTable'[EVA%]) > 0, 1, 0)

   ```

 I tried to write DAX in measure table as below, but still getting same error -

```DAX

EVA% Greater Than Zero =

IF(

    ISFILTERED('CountryTable'[Country Name]),

    IF(

        CALCULATE(MAX('MeasureTable'[EVA%])) > 0,

        1,

        0

    ),

    IF(

        MAX('MeasureTable'[EVA%]) > 0,

        1,

        0

    )

)

```

Created below as calculated column in country table-it is not working and giving same error -

   ```DAX

   EVA% Greater Than Zero =

   VAR MaxEVA = CALCULATE(MAX('MeasureTable'[EVA%]), 'CountryTable'[Country Name] = EARLIER('CountryTable'[Country Name]))

   RETURN

   IF(MaxEVA > 0, 1, 0)

   ```

Below 2 DAX's is not gibving error but it is not working, its not returning anything -

EVA%_Max = MAXX('Measure Table', [EVA%])

EVA%_Max_greaterthanZero = IF([EVA%_Max ]>0, 1,0)

Please help me with a work around.

Thankyou in advance

[Sania-F]

After n no. of hits and trials the below DAX worked out. 
So, I created a new mesaure in my measure table as follows -

```DAX

CountryList_EVA_Positive =

    CONCATENATEX(

        FILTER(

            VALUES('dimCountry'[Country]),

            [EVA%] > 0

        ),

        'dimCountry'[Country],

        ", "

    )

Finally I dynamically inserted this CountryList_EVA_Positive measure in text box, and it worked as expected. 
------------------------------------------------------------------
Just to reiterate the problem statement - In power Bi, I have a measure table which has only measures. I have a dimension table with country column. These 2 are not at all connected in any way, no relationship, no common columns. There is a measure called "cci%" Now my requirement is -In a text box, I want to be able to get all country names from dimCountry where the cci%>0.

[Anonymous]

Great! You have created an elegant measure to solve this problem!

[Anonymous · Accepted solution]

Hi @Sania-F 

Not sure if I understand it correctly. Below is my example. To display some dynamic values in a textbox, you need to create a measure to calculate the dynamic value. In addition, as a measure returns only one scalar value, you have to concatenate all filtered countries into a value. So I use CONCATENATEX function. 

Without knowing your MeasureTable's structure, I assume that it has [CountryName] column too, so I filter MeasureTable and use its Country Name column in my sample measure. You may have to adjust it according to your data model. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Sania-F]

Hi @v-jingzhan-msft ,

Thankyou for your response. My measure table does not have country column. Only the dimcountry table  has "country" column. And there is no relationship between "measure table" and " dimcountry" table. So how can I concatenate country names now? 

PREVIEW

[Anonymous]

@Sania-F 

Does the Measure Table has other column like Country ID or something similar that also exists in DimCountry table? 

You can try this new measure. Assume that you only have Country code in measure table, you can filter measure table to have those country codes whose EVA% are greater than 0, then use these country codes to filter DimCountry table to find the corresponding country names and concatenate them finally. TREATAS function can be used when a relationship does not exist between the tables.

EVA% Greater Than Zero 2 = 
var _countryCodes = SELECTCOLUMNS(FILTER(MeasureTable,MeasureTable[EVA%]>0),"country_code",'MeasureTable'[Country Code])
return
CALCULATE(CONCATENATEX(DimCountry,DimCountry[Country Name],", "),TREATAS(_countryCodes,DimCountry[Country Code]))

Best Regards,
Jing

[Sania-F]

HI Jing,

unfortunately my measure table and dimension table do not have anything in common 😞

So I was struggling with writing DAX for my requirement.Found a workaround for this situation. 

I observed that your pbi has EVA% as a column, but in my case it was a measure that was calculated based on xyz values. And the measure doesnt store data, which means we cannot use eva% measure directly inside FILTER function of DAX to filter values. So had to use it along with other DAX function.

Thanks a lot for your help. It redirected me towards my solution.