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 Add...
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

     

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.