Forum Discussion

Covington's avatar
Covington
Helper I
7 years ago
Solved

Find Differences in column

I have a set of data that I am querying out of a database that has the following fields:

 

  • Node
  • Tag
  • Name
  • Address

 

There are multiple nodes that *may* have a Unique Address.  Mostly, the Addresses are the same.   I have already pulled in and massaged the query to produce the above fields sorted by Tag.  Now I want to show only the tags that have different addresses (but same Name).

 

The trick here is that there could be 5 Nodes having the same Tag, but maybe only 1 address difference between all 5 - but I need to see all 5 of them.  

 

I would do this manually, but there are 50,000+ tags :(

 

Thanks, hopefully this makes sense.

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi Covington 

     

    Not sure if I understood the requirement, but try this code, it will return true if there is more then one address variation per tag.

     

    Column = 
CALCULATE( 
    DISTINCTCOUNT( YourTable[Address] ), 
    ALLEXCEPT( YourTable, YourTable[Tag] ) 
) > 1

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Covington 

     

    Not sure if I understood the requirement, but try this code, it will return true if there is more then one address variation per tag.

     

    Column = 
CALCULATE( 
    DISTINCTCOUNT( YourTable[Address] ), 
    ALLEXCEPT( YourTable, YourTable[Tag] ) 
) > 1

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • jarretl's avatar
    jarretl
    Advocate I
    7 years ago

    Hey Covington,
    Could you do this in the source sql as an additional "flag" column on the final output table you use to import into powerbi?

    IIF(MIN(Address) OVER (PARTITION BY Node, Tag, Name) <> MAX(Address) OVER (PARTITION BY Node, Tag, Name), 1, 0) AS FLAG

    Then flag would be 1 for any combination of Node/Tag/Name that had more than one address, and 0 for any grouping of Node/Tag/Name that has a single address.
    From there, you could filter your powerbi reporting where Flag=1 to return all results for Node/Tag/Name groups where there are more than one address...

    Not sure if i'm interpreting your question correctly, hopefully i'm on the right track at least.  There may be a way to do something similar as a calculated column within the powerbi mode, but I suspect it would end up being more complicated than using partition functions in SQL.

     

    -Jarret.