Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Advocate I
Advocate I

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
Community Champion
Community Champion

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

 

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.