Search code examples
powerbidaxpowerbi-desktop

Removing duplicate values in table chart

I would like to ask for your assistance, the table chart shows duplicate values but in power query it is just unique. Is there are DAX formula where it will be distinct in the table chart? Apologies as I am new to power bi. Please see sample images

I want to remove or now display the "0" value with duplicate competency.

Thank you

I want to remove or now display the "0" value with duplicate competency.

Solution

  • You can try this simple DAX code as a new measure:

    Value Measure = 
VAR _val = SELECTEDVALUE ( 'Table'[Value] )
RETURN
IF ( _val <> 0 , _val )

    Use the measure in your table visualization together with the 'Table'[Competency] column, and remove the 'Table'[Value] column from the visual.

    This removes all the competencies with zero-values - not sure if that is what you were after, or if you only wanted to remove exactly duplicates, that requires a slightly different formula, like this:

    Advanced Value Measure =
VAR _good_symbol =
    UNICHAR ( 10003 )
VAR _bad_symbol =
    UNICHAR ( 10007 )
VAR _sum =
    SUMX ( 'Table', IF ( 'Table'[Value] = _good_symbol, 1 ) )
VAR _is_distinct =
    HASONEVALUE ( 'Table'[Competency] )
RETURN
    SWITCH (
        TRUE (),
        _is_distinct
            && _sum > 0, _good_symbol,
        _is_distinct
            && _sum = 0, _bad_symbol
    )

    This will show all the competencies, but assign a ✗ to the competencies that have no rows with ✓.