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. ...
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)

     

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.