Finding unique and intersect values using DAX URGENTTT

[abiyevnijat]

Hello guys,

I uploaded a PowerBI file and I have 1 table in this file. In this table some of my main_id column gets only 1 kre_id value, but some of them gets 2 or more kre_id values. For example, I want to find how much my kre_id=84 and col_id =15 value gets unique main_id (which dont have same main_id when I filter col_id =15). And my second request is when I filter col_id =15 how much my_main intersect with kre_id=84. For example how much main_id has both 81 and 84, 82 and 84, 83 and 84, 85 and 86. For example in my table main_id = 72 gets 2 values (kre_id =84 and kre_id =85). I dont want to show this main_id in unique values of kre_id =84 and kre_id =85 and i want to show (Number of intersection kre_id =84 and kre_id =85 is 1) for example. Please write the Dax formula of this problem.

[amustafa · Accepted solution]

Adjust the first measure djust the logic to focus exclusively on `col_id = 15`. We want to count unique `main_id` values where `kre_id = 84` for `col_id = 15`, ensuring these `main_id` values do not have any other `kre_id` values associated with them within `col_id = 15`.

Adjusted Measure 1: Unique `main_id` for `kre_id = 84` within `col_id = 15`

Unique Main ID for KreID 84 and ColID 15 =
VAR FilteredTable =
FILTER (
YourTable,
YourTable[col_id] = 15
)
VAR MainIDsWithKreID84 =
CALCULATETABLE (
VALUES (FilteredTable[main_id]),
FilteredTable[kre_id] = 84
)
VAR MainIDsWithSingleKreID =
FILTER (
MainIDsWithKreID84,
CALCULATE (
COUNTROWS (FilteredTable),
ALLEXCEPT (FilteredTable, FilteredTable[main_id])
) = 1
)
RETURN
COUNTROWS (MainIDsWithSingleKreID)

[abiyevnijat]

Hello amustafa,

Thanks for answering my question. In second formula when I change CurrentKreID = 85, I find number of main_id which gets in kre_id (84 and 85). And this is what I want. But in first part I need to find number of unique main_ids which give only the result kre_id = 84. But when you do this compare only with col_id = 15 results. Because in other col_ids results there chance to be my main_id get different result in kre_id. I dont want this main_ids influence my result. I want only compare my main_id column with col_id = 15s kre_ids.

[amustafa]

From your problem description alone, you need 2 measures. Please follow the guidelines on how to submut a problem in this forum: How to Get Your Question Answered Quickly - Microsoft Fabric Community

Measure 1: Unique `main_id` Count for `kre_id = 84` and `col_id = 15`

1. Filter the table to include only rows where `kre_id = 84` and `col_id = 15`.
2. Count the unique `main_id` that do not appear with any other `kre_id`.

Unique Main ID Count =
VAR UniqueMainIDs =
CALCULATETABLE (
VALUES (YourTable[main_id]),
YourTable[kre_id] = 84,
YourTable[col_id] = 15
)
VAR MainIDsWithSingleKreID =
FILTER (
UniqueMainIDs,
CALCULATE (
COUNTROWS (YourTable),
ALLEXCEPT (YourTable, YourTable[main_id])
) = 1
)
RETURN
COUNTROWS (MainIDsWithSingleKreID)

Measure 2: Intersection Count for `kre_id = 84` with Other `kre_id` Values

This measure will be a bit more complex. We need to count intersections of `main_id` for each `kre_id` pair with `kre_id = 84` when `col_id = 15`.

1. Create a table that lists all `main_id` values associated with `kre_id = 84` and `col_id = 15`.
2. For each `kre_id`, count how many `main_id` values intersect with those in step 1, excluding `kre_id = 84`.

Intersection Count =
VAR KreID84MainIDs =
CALCULATETABLE (
VALUES (YourTable[main_id]),
YourTable[kre_id] = 84,
YourTable[col_id] = 15
)
RETURN
SUMX (
VALUES (YourTable[kre_id]),
VAR CurrentKreID = YourTable[kre_id]
VAR IntersectionCount =
IF (
CurrentKreID <> 84,
COUNTROWS (
INTERSECT (
KreID84MainIDs,
CALCULATETABLE (
VALUES (YourTable[main_id]),
YourTable[kre_id] = CurrentKreID,
YourTable[col_id] = 15
)
)
),
BLANK()
)
RETURN
IntersectionCount
)

This measure will give you a table of counts for each `kre_id` intersecting with `kre_id = 84`. You might need to adjust these formulas based on your specific data structure and requirements. Remember to replace `YourTable` with your actual table name.