Forum Discussion

koko_pbi's avatar
koko_pbi
Frequent Visitor
3 years ago
Solved

Discrepancy in Measure Results Between Different Visualization Types

hello everyone, 

i need your help , 

 

I'm encountering an issue with a DAX measure in Power BI where the results differ between different types of visualizations on the same report page. The measure is intended to count occurrences of a specific keyword within a text column. However, I'm observing inconsistent behavior when comparing card, table, and graph visualizations.


- i did try with two dax function , same result,  

 

LocationCount = 
var location_table = 
    FILTER(
        'all content', 
        CONTAINSSTRING(
            'all content'[Location(s)], 
            SELECTEDVALUE(
                'location list'[Location(s)]
                )
            )
        )
var location_number = 
   COUNTROWS(location_table)


return 
  location_number 
VAR LocationPartsCount =
SUMX(
    'all content',
    IF(
        SEARCH(
              SELECTEDVALUE('location list'[Location(s)]),
                'all content'[Location(s)], 1, 0) > 0,
            1,
            0
        )
    )
RETURN LocationPartsCount

 


- In a card visualization, the measure seems to work correctly, providing the expected count of occurrences.
- However, when the same measure is used in a table or graph visualization, it only counts occurrences where the keyword is  equal (=) and not when it's part of a larger text (e.g., "Mongolia|United States|other county").


- I've double-checked the measure's DAX syntax and data model to ensure accuracy.
- I've reviewed the columns and filters used in both table and graph visualizations to match the setup in the card visualization.
- Data has been refreshed to ensure changes are reflected accurately.

 

=>  the keywords are in separate table
but what is weird in this case, is that it woks perfectly for other column,  like tags column, or names 
but not for location.

do you have any idea ?

 

thank you 

if i can expliain more, plz tell me

  • hey, 

    i did found where the problem was , 

    power bi did create from alone a relation between my tables , what caused the misscount

    good day ! 

     

6 Replies

  • foodd's avatar
    foodd
    Community Champion

    Thank you for your description.   Would you please add your work-in-progress Power BI Desktop file so that members of the forum may better appreciate the state.   

    • koko_pbi's avatar
      koko_pbi
      Frequent Visitor

      hello, 

      sorry but i did'nt understand what you ask for ?
      the data i'm working with ?


      let say : 
      i have 
      col1        col2       col3        col4            location (as col 5)
      value1.1   value2.1    value3.1    value4.1  Mongolia|United States
      value1.2   value2.2    value3.2    value4.2  Mongolia

      value1.3   value2.3    value3.3    value4.3  Mongolia|Germany

      value1.4   value2.4    value3.4    value4.4  Mongolia|France

       

      through my dax measure i want to calculate the occurencies for each country for exemple ,

      i did extract all unique country from this col to list, that i convert into a sigle table col 
      then i check if a country in the list is in the location(as col 5)
      country list table
      country (col)

      Mongolia

      United States

      Germany

      France
      United Kingdom
      ...

       

       

      but my measure count only line where the country = to the country in location (it seems like it use "=" and not "in" operator) even with "containsstring" 

      it will count me 

      Mongolia  -> 1 ,   but the country is repeated 4 times 

       

      i do use the same function for other cols , with the same process, and it works fine , but here not !!! 

      if you have any idea 

      thanks

       

      • foodd's avatar
        foodd
        Community Champion

        Looking for your Power BI Desktop file.

  • koko_pbi's avatar
    koko_pbi
    Frequent Visitor

    no one knows what wrong ??

    i did test somethings 

    i think  there is some metadata in there, 

     

    because when i create myself the coutries and enter the value into new table , iget the correct count ,

    but with the automation process, United States for exemple get only where United States is as alone word, but not within a list United States|France|Germany

     

    i did text.clean , change the type and use other extract process, always the wrong result if it comes from the main table !!! really weird 
    do you have any idea how i can detect the metadata or delete them ?

    thanks

  • koko_pbi's avatar
    koko_pbi
    Frequent Visitor

    hey, 

    i did found where the problem was , 

    power bi did create from alone a relation between my tables , what caused the misscount

    good day !