Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a set of data that I am querying out of a database that has the following fields:
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.
Solved! Go to Solution.
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
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.
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
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |