cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Covington
Helper I
Helper I

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.

 

 

1 ACCEPTED SOLUTION
Mariusz
Super User II
Super User II

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

 

View solution in original post

3 REPLIES 3
jarretl
Frequent Visitor

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.

Mariusz
Super User II
Super User II

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

 

View solution in original post

parry2k
Super User III
Super User III

@Covington can you share sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors