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,
I would appreciate any help with the following:
Goal: Create a table that identifies the following:
I have three fact tables that identifies software installed by a server name and a product name. Each fact table represents a different discovery tool.
I created a dimension table for unique product names. This table is linked to the three fact tables.
I created a Power BI Report Table that identifies the unique product name (I call it Brand Product Name), distinct servers that use said product and then I have a column for each fact table (Discovery Tool) which identifies if the server is found within each tool environment. It looks something like this:
Discovery Sources | ||||
Product Name | Distinct Server name | Tool A | Tool B | Tool C |
Brand Product Name | Server 1 | 1 | ||
Brand Product Name | Server 2 | 1 | 1 | |
Brand Product Name | Server 3 | 1 | 1 | 1 |
Brand Product Name | Server 4 | 1 | 1 | 1 |
Brand Product Name | Server 5 | 1 | 1 | 1 |
Brand Product Name | Server 6 | 1 | 1 | 1 |
Brand Product Name | Server 7 | 1 | 1 | 1 |
Brand Product Name | Server 8 | 1 | 1 | 1 |
Brand Product Name | Server 9 | 1 | 1 | 1 |
Brand Product Name | Server 10 |
I want to produce a single table that tells me for all distinct products and distinct servers within each product, which distinct servers from Tool A, are not found in Tool B and Tool C.
Additionally, I want a table that does the reverse, telling me which servers in Tool B and C, are not in A. I hope this makes sense. This is a pattern that is really needed in the software licensing area so it would be big win to have something in place.
Thanks,
Tim
Solved! Go to Solution.
Thank you again. This appears to work. Funny, this was not what I was expecting. The measure approach worked, I assume, because of the model I have in place plus each measure is evaluated on its own. So, if I have 4 measures applied against the model, I will get the results specific to each measure. At least, this is the way that it appears.
This was very helpful.
Tim
Hi Tim,
What if you created a measure that returns a 1 only if 'Tool A' = 1:
Only Tool A = IF([Tool A] = 1 && ISBLANK([Tool B]) && ISBLANK([Tool C]);1;blank())
For your other logic you can create seperate measures.
Regards,
Dries
Thank you. I applied the measure as you indicated below and it works. Yet, adding the others to see if it give me all I need. I am knew to this so it may take another day to provide additional feedback.
Tim
Thank you again. This appears to work. Funny, this was not what I was expecting. The measure approach worked, I assume, because of the model I have in place plus each measure is evaluated on its own. So, if I have 4 measures applied against the model, I will get the results specific to each measure. At least, this is the way that it appears.
This was very helpful.
Tim
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |