Create a new table from distinct values with conditions: Common Problem in Software Licensing area

[timothystacy]

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

[Bitwize_PowerBI]

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

[timothystacy]

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

[timothystacy · Accepted 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