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.
Hi all,
I have a set of tables I want to build a visual from to show, for a customer, all available products to them, and any sales that have already happened. Here is the table structure (the formatting isn't helpful on here for tables but thought the data can be copied and pasted out easily to recreate the structure)
Customers table
ID | Area |
C1 | Germany |
C2 | Germany |
C3 | France |
C4 | Spain |
Product availability table
Area | Product |
Germany | A |
Germany | B |
Germany | C |
France | A |
France | B |
France | C |
Spain | A |
Spain | B |
Spain | C |
Spain | D |
Sales table
ID | Product | Quantity |
C1 | A | 10 |
C1 | B | 20 |
C1 | C | 30 |
C2 | A | 100 |
C2 | C | 50 |
C4 | B | 25 |
The way i want the final visual to work is:
So depending on the customer selected, the visual would work as follows:
Though the relationships between tables is intuitive, I can't figure out how to achieve the visual when combining with "Show items with no data" because (for example) doing so incorrectly shows customer C2 having product D being available.
I also want to introduce a "time available" dimension to the model want to understand how to do this generically that I can extend to other columns, if that's possible!
How can this be done?
Thanks for any help or advice!
Solved! Go to Solution.
I tried to add a area table and create relationship as below.
Then create a measure as below and then the visual show as expected.
Quantity measure = VAR val = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( Sales, Sales[Product] = LASTNONBLANK ( 'Product availability'[Product], "" ) ) ) RETURN IF ( ISBLANK ( val ) && NOT ( ISBLANK ( LASTNONBLANK ( 'Product availability'[Product], "" ) ) ), 0, val )
See more details in the attached pbix file.
I tried to add a area table and create relationship as below.
Then create a measure as below and then the visual show as expected.
Quantity measure = VAR val = CALCULATE ( SUM ( Sales[Quantity] ), FILTER ( Sales, Sales[Product] = LASTNONBLANK ( 'Product availability'[Product], "" ) ) ) RETURN IF ( ISBLANK ( val ) && NOT ( ISBLANK ( LASTNONBLANK ( 'Product availability'[Product], "" ) ) ), 0, val )
See more details in the attached pbix file.
Eric, thanks very much for your help on this - works perfectly, just what I wanted.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |