Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have two datasets. One showing the available Inventory of Products over time, the other Products Sold and Part IDs.
In a dashboard, I have to show all Products both Sold and Available. This "status" will be determined by whether an ID appears in both datasets or not.
**This is where I am struggling most. I need a solution that addresses a Many:1 relationship of repeating Item IDs in the Sold data, and a unique Item ID in inventory during the match process. Many of Power BIs functions require unique IDs in each of two columns to "match" between two datasets.
In the dashboard, I'd like to show the following in a hierarchical table
I have created (2) sample datasets, one representing Available Inventory, the other what's Sold.
https://www.dropbox.com/s/gbzdwo6qxxt026x/Sample%20Data.xlsx?dl=0
Thanks in advance. Appreciate a link to a .pbix file if possible.
Solved! Go to Solution.
@Anonymous,
Create a column in the Inventory table using DAX below.
Status = IF(CALCULATE(COUNTROWS(Sold), FILTER( Sold, Sold[Item ID] = EARLIER(Inventory[Item ID]) ) ) > 0,"Sold","Available")
Create table visuals as below.
Regards,
Lydia
@Anonymous,
Create a column in the Inventory table using DAX below.
Status = IF(CALCULATE(COUNTROWS(Sold), FILTER( Sold, Sold[Item ID] = EARLIER(Inventory[Item ID]) ) ) > 0,"Sold","Available")
Create table visuals as below.
Regards,
Lydia
This worked wonderfully. Can you break down the dax statement so I understand what's happening, and how it works?
Thanks!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
88 | |
85 | |
68 | |
68 | |
64 |
User | Count |
---|---|
209 | |
118 | |
116 | |
81 | |
74 |