Forum Discussion

abiyevnijat's avatar
abiyevnijat
Frequent Visitor
2 years ago
Solved

Finding unique and intersect values using DAX URGENTTT



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.

Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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)

     

3 Replies

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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)

     

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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.

     

     

  • abiyevnijat's avatar
    abiyevnijat
    Frequent Visitor
    2 years ago

    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.