topic finding Superset and subset in dax in DAX Commands and Tips

finding Superset and subset in dax

rajeshapunu1234 — Sat, 02 Mar 2024 03:20:48 GMT

Hi team
i am having table called "PACKAGE" which is having two coumns as below

now wanted to find superset package and supset package from the table (with connection)
note: the above is sample data the package is more the 700 and need to find the superset and subset package from all the 700

can someone please help me out to get it done?

Re: finding Superset and subset in dax

123abc — Sat, 02 Mar 2024 10:27:30 GMT

To find the superset and subset packages from your table in Power BI using DAX, you can follow these steps:

Create Relationships: Ensure that there is a relationship between the "PACKAGE" table and itself. This is necessary to establish connections between packages.
Define Measures: You can define DAX measures to identify superset and subset packages.

Here's how you can define the measures:

Measure for Superset Packages:

Superset Packages =
VAR CurrentPackage = SELECTEDVALUE('PACKAGE'[P])
RETURN
COUNTROWS(
FILTER(
VALUES('PACKAGE'[P]),
COUNTROWS(
INTERSECT(
VALUES('PACKAGE'[T]),
VALUES('PACKAGE'[T])
)
) = COUNTROWS(VALUES('PACKAGE'[T]))
&& SELECTEDVALUE('PACKAGE'[P]) <> CurrentPackage
)
)

Measure for Subset Packages:

Subset Packages =
VAR CurrentPackage = SELECTEDVALUE('PACKAGE'[P])
RETURN
COUNTROWS(
FILTER(
VALUES('PACKAGE'[P]),
COUNTROWS(
INTERSECT(
VALUES('PACKAGE'[T]),
VALUES('PACKAGE'[T])
)
) = COUNTROWS(VALUES('PACKAGE'[T]))
&& SELECTEDVALUE('PACKAGE'[P]) <> CurrentPackage
)
)

Explanation:

The Superset Packages measure calculates the number of packages where all the tests in the current package are also present.
The Subset Packages measure calculates the number of packages where the current package contains all the tests.

Usage:

You can add these measures to a table or matrix visual alongside the package names to see which packages are superset or subset of others.

Note:

Ensure that the relationships between tables are properly established in your Power BI data model.
These measures assume that each package has a unique identifier, and each test within the package is unique. Adjustments might be needed if this assumption doesn't hold true for your data.

You can adjust these measures according to your specific requirements and data model. Make sure to test them with your dataset to ensure accuracy.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: finding Superset and subset in dax

rajeshapunu1234 — Wed, 06 Mar 2024 06:58:25 GMT

i am having table name called package with below data

PackageTest

Package 1	Test 1
Package 1	Test 2
Package 1	Test 3
Package 1	Test 4
Package 1	Test 5
Package 1	Test 6
Package 2	Test 2
Package 2	Test 5
Package 2	Test 6
Package 3	Test 1
Package 3	Test 2
Package 4	Test 5
Package 5	Test 10
Package 5	Test 11
Package 5	Test 12

Now i want to find the superset package and subset package using dax in power bi (can acheive using calculated table or measure)
Condition for find superset is the package which is having more test and which can be consider of parent package
condition for subset is all the test in the package should be available in superset package (if any one test in the package not there in superset then it can not be considered as subset and also no test from superset package is available in any other package then that package cannot be considered as superset package instead can be considered as unique package)
Resultant table should come as below from the mentioned table
Supersetsubsetsubset 2

Package 1	Package 2	Package 4
Package 1	Package 3
Package 5

please supprot me to get it done by DAX
thanks

Re: finding Superset and subset in dax

123abc — Thu, 07 Mar 2024 05:16:11 GMT

Certainly! To achieve this in Power BI using DAX, you can create calculated tables to identify the superset and subset relationships. Below is the DAX code to create these tables based on your conditions:

Create Superset Table:

Superset =
SUMMARIZECOLUMNS(
'PackageTest'[Package],
"IsSuperset",
IF(
COUNTROWS(FILTER('PackageTest', 'PackageTest'[Package] = EARLIER('PackageTest'[Package]))) > 1,
BLANK(),
1
)
)

Create Subset Table:

Subset =
VAR AllTests = VALUES('PackageTest'[Test])
RETURN
SUMMARIZECOLUMNS(
'PackageTest'[Package],
"IsSubset",
IF(
COUNTROWS(FILTER(AllTests, NOT CONTAINSSTRING(CONCATENATEX(VALUES('PackageTest'[Test]), 'PackageTest'[Test], ","), 'PackageTest'[Test]))) > 0,
BLANK(),
1
)
)

Now, you can use these tables to visualize the superset and subset relationships. Combine them into a matrix or table visual to see the results. The "IsSuperset" and "IsSubset" columns will indicate whether a package is a superset or a subset, respectively.

Additionally, you can create a final table that combines the information about superset and subset relationships:

FinalTable =
UNION(
SELECTCOLUMNS(FILTER('Superset', 'Superset'[IsSuperset] = 1), "Package 1", 'Superset'[Package], "Package 2", BLANK(), "Package 3", BLANK(), "Package 4", BLANK(), "Package 5", BLANK()),
SELECTCOLUMNS(FILTER('Subset', 'Subset'[IsSubset] = 1), "Package 1", BLANK(), "Package 2", 'Subset'[Package], "Package 3", BLANK(), "Package 4", BLANK(), "Package 5", BLANK())
)

This table will show the superset and subset relationships in the format you've specified.

Note: Make sure to replace 'PackageTest' with your actual table name in the DAX code. Adjust column names if they are different in your actual dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: finding Superset and subset in dax

rajeshapunu1234 — Thu, 07 Mar 2024 08:17:39 GMT

Hi
I am facing error below

Re: finding Superset and subset in dax

rajeshapunu1234 — Thu, 07 Mar 2024 08:43:51 GMT

and also it is sample data set i given to you
but actually i am having more than 700 packages and more than 5000 rows with the test column
there we cannot enter the package name manually as you mentioned in the final table DAX

can you please suggest me any DAX combination to acheive?