Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Im a bit of a novice still to PBI.
I am trying to figure out or know if its possible to create a formula/visual that can show me the cross reference between items in my data. For example, within my dataset I have customers, orders, location, items. What I am trying to find is if a customer is ordering from one or more locations (factory) and if they are ordering more than one type of item. I want each of those seperate on their own then ultimately finding the customer that intersects those two references too so I would end up with three outputs, which customers order from multiple locations, which customers order more than one type of item, and which customers both order more than one item from multiple locations.
Perhaps a matrix view that can highlight these cross references? Or open to other ideas how to display the information.
Thanks. Appreciate any help you can provide.
Solved! Go to Solution.
Hi, @MSW
Please check the following methods.
Measure:
Color =
Var _Countlocation =CALCULATE(DISTINCTCOUNT('Table'[Location]),ALLEXCEPT('Table','Table'[Customer ID]))
Var _Countorder=CALCULATE(DISTINCTCOUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Customer ID]))
return
IF(_Countlocation>1&&_Countorder>1,"Green",IF(_Countlocation>1,"Red",IF(_Countorder>1,"Blue")))
Adds a color to each value in the list in conditional format.
Does this meet your desired outcome?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
All of the above are possible in PBI/DAX. But... how you want to show it - up to you. If you want someone to build a prototype, you'll have to supply some data to tinker with and, what's important, some correct output you'd like to see from the data. So, we need input and output to build something that at least will be correct. Then you can think about how to use the created measures in your visuals.
Of course, the table looks like this. The rows highlighted in red would show up in the matrix as they meet one of the criteria, a customer ordering the same item from multiple locations. The blue would be highlighted as it meets the criteria of ordering muliple types of items. Then the one in green would show up as it meets both criteria. *The overlap between the two criteria.
Customer ID | Order | Item | Location |
123 | ABC | 4567 | USA |
123 | ABC | 4567 | China |
345 | DCB | 896 | USA |
345 | BNC | 896 | USA |
567 | NBA | 1090 | France |
567 | NBA | 1090 | France |
890 | LMN | 2087 | USA |
890 | LMN | 2087 | France |
890 | HCS | 8076 | Peru |
Hi, @MSW
Please check the following methods.
Measure:
Color =
Var _Countlocation =CALCULATE(DISTINCTCOUNT('Table'[Location]),ALLEXCEPT('Table','Table'[Customer ID]))
Var _Countorder=CALCULATE(DISTINCTCOUNT('Table'[Order]),ALLEXCEPT('Table','Table'[Customer ID]))
return
IF(_Countlocation>1&&_Countorder>1,"Green",IF(_Countlocation>1,"Red",IF(_Countorder>1,"Blue")))
Adds a color to each value in the list in conditional format.
Does this meet your desired outcome?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |